Hi,I am having issue with out Transaction logs as it's keep getting full even after shrinking the file.We have Sql server 2008 and it's also run MS CRM application.We have T -Log Back up is set up to every 2 hrs.I have changed my recovery model from FULL to SIMPLE then Shrink the file and did full back up and set up again to FULL Recovery Mode but still it's growing fast.

As Chad suggested, it is likely that you just have lot of activity, resulting in lot of writes to the log file. If that is the case, you do need to backup the logs more frequently (or live with the large log file).

What was not clear to me from your posting though, is whether the VLF's are cleared when you take the log backup. Immediately after you do a log backup, run DBCC loginfo. You should see most VLF's with status = 0. If you see them with status = 2, that means they cannot be cleared. Run DBCC opentran to see the oldest transactions.

Thanks Chad.I will change Log back up frequency from 2 hours to every one hour, do you think it will cause any disk space issue?I ran DBCC Loginfo command and lot of file shows with status = 2 as we just finished T-Log back up and I ran the query after 10 minutes.Is it I need to run frequently DBCC Loginfo command and monitor it?What you suggest if any FSeqNo has status = 2?Is it CreateLSN is 0 then what is indicating in very first two rows?

I ran DBCC LOGINFO command and lot of files with status = 2 and total files are 295 with size from 253952 to 16384000.I also ran DBCC OpenTran but no open transaction.Please advice me, Appreciate your help!

I ran DBCC LOGINFO command and lot of files with status = 2 and total files are 295 with size from 253952 to 16384000.I also ran DBCC OpenTran but no open transaction.Please advice me, Appreciate your help!

Thanks.

I am not familiar with SPGetSQLPerfStat. Seems like it is a query that is trying to figure out log reuse wait info. But, for your log file size, I see conflicting info in your posting - initially you said it is 90.6 and then 5403256. We need to figure out what the actual size is and how much of it is used.

Another thing that I am not clear from your postings is whether or not log backups clear any VLF's. Not all VLF's would be cleared; in a busy database there would be many that are active, especially if there are long running transactions. And it is not just long running transactions - other things such as transactional replication, database mirroring etc. can cause VLF's to remain active even if there are no uncommitted transactions.

In any case, do the following:

1. Run the following:

DBCC SQLPERF(LogSpace)

This should give you info on log file usage and percentage used for each database. This is to determine what the log file sizes are, what percentages are used and to confirm that your CRM database is in-fact the one causing the problem.

2. Run the following script:

select name,log_reuse_wait_desc from sys.databases

That should tell you the reasons why the log file for each database cannot be cleared. From your posting, it seems like it is LOG_BACKUP that is given as the reason. Let us assume that is still the case.

3. In your log backup script, make sure that you DO NOT have WITH COPY_ONLY option.

4. Run the following and copy and save the results somewhere

DBCC LogInfo

5. Backup the log file manually once (make sure there is no WITH COPY_ONLY option!).

7. Run DBCC LogInfo again and compare with what you got in step 4. I am expecting that there should be many more VLF's with status = 0.

8. If you see that no VLF's have been cleared (Fstatus = 0 is a cleared VLF and Fstatus = 2 is an active VLF), "DBCC SQLPERF(LogSpace)" and "select name,log_reuse_wait_desc from sys.databases" again to see the space usage and to understand what is holding up the VLFs.

9. If you still see a problem, post the log_reuse_wait_desc and also the results of "DBCC LoginInfo". When you post, use [code] and [/code] tags so it is readable. I probably won't be able to offer much help without having access to the database, but some of the others on the forum might have better insights.

poratips, I don't see any problems with your log file. After your log backup the log file is TINY at under 2 Megs. In fact, it was tiny even before you did the backup, perhaps because your scheduled job was functioning as designed. If your log file is growing too big to tens or hundereds of gigabytes, simply increase the frequency of log backups to once per hour, or once every half hour, or whatever will get you to the log size that your disk space restrictions can tolerate. There are no adverse effects to increasing the frequency of log backups (unless you went overboard )

Also, as a general best practice, you should:

1. Always ALLOW log file to autogrow.

2. Never LET log file to autogrow if you can help it.

What I mean is that, you should look at your work load and figure out how much log space you need. (Observe how big the log file gets to over a period of 2 or 3 typical days). Allocate that much or a bit more, so SQL Server won't have to autogrow the log file. If log file has to autogrow, all other activities on the database have to wait. What is even worse is that newly allocated VLFs have to be zeroed out - they cannot be added via instant initialization. So autogrowth of the log file can noticeably pause the database.

BUT, leave the autogrow capability enabled, because in case it needs to autogrow and you don't let it, all activities on the database will stop.

I don't know but now it shows from DBCC SQLPERF(LogSpace)and also it show lots of VLs - and log_reuse_wait_desc shows "NOTHING" and DBCC LogInfo shows now 490 VLFs with only one has status = 0Log Size (MB) Log Space Used (%) Status

I don't know but now it shows from DBCC SQLPERF(LogSpace)and also it show lots of VLs - and log_reuse_wait_desc shows "NOTHING" and DBCC LogInfo shows now 490 VLFs with only one has status = 0Log Size (MB) Log Space Used (%) Status

14198.8 0.6455484 0

When you saw this, how long ago was the last log backup? If 14 Gigs is too much, you should backup the log file more frequently.

When you backup the log file, any VLFs that can be cleared are cleared. As time goes on, more of them get consumed (so status changes to 2). So watch the usage and set the backup interval to be short enough to meet your disk usage requirements.

Thanks James.When I posted during that time I saw yesterday and size is very.I have just checked - select name,log_reuse_wait_desc from sys.databases and log_reuse_wait_desc shows Log_backup and also checking to VLfs, total files around 490 and 33 files with status = 2.I have changed T-log backup frequency to every one hour, now backup log size is every hour is:8 AM 7013 KB9 AM 24897 KB10 AM 45385 KB11 AM 43220 KB12 PM 419318 KB

I didn't follow what you said this: "When I posted during that time I saw yesterday and size is very."

For the rest of the data, everything seems normal to me. During business hours, it is under half a gig - that is good. From 10:00 AM to 11:00 AM, the size went down - which indicates that your log backups are doing its job. So I don't see anything wrong at all - are you experiencing any problems?

Only 0.57 percent is used - which is consistent with most of the VLF's being in cleared state. So while the log file itself may be large (extrapolating from what you said, it looks like it might be about 8 Gigs), most of the space in that 8 gigs is availabe for reuse.

The physical file may have grown probably because there was lot of activity between successive log back ups, long running queries, or large index rebuilds. If you expect them on a regular or periodic basis, you should leave the log file at that size and continue to do regular log backups.

Well, lot has been discussed on log file growth and some tips/tricks. If I were you, I would start with why the log file is growing huge? Find out the root cause and then start performing the tips. Last time, it happened with me was when we were rebuilding Index and also while creating Index for large database table. Also, it happens when some user queries. Check out the queries that were running on this database which is causing the log file grow and perform tuning the sql, this would help to great extent.