Pages

Tuesday, April 26, 2011

SQL Transaction Logs Grow Continuously

In an attempt to get some more content on my blog, I'm going to repost some documentation for things I've learned that may be useful to others.



Description
If the SQL log files (.ldf) keep growing bigger and bigger, it is not normal behavior. This document provides troubleshooting steps to help determine the cause and some ways to re-claim disk space.

For more information about SQL Transaction Logs and Database Recovery Models review the following information:


Note: In DPM, recovery points created using express full backups are SQL full backups. Synchronizations or recovery points created using incremental backups are SQL transaction log backups. Any database that has a recovery model of full or bulk logged needs to have incremental backups (synchronizations) to truncate the log files.

Check Recovery Model and Log Reuse Wait
1.      Open SQL Management Studio and connect to the server.
2.      Click New Query.
3.      Type the following SQL Command and click Execute:
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases
4.      Find the database with the log file that is continuing to grow and note the values of the recovery_model_desc and log_reuse_wait_desc columns (example below):
Name
Recovery Model
Log Reuse Wait
DB1
BULK_LOGGED
LOG_BACKUP
DB2
BULK_LOGGED
LOG_BACKUP
DB3
FULL
NOTHING
DB4
SIMPLE
NOTHING
             
            The Log Reuse Wait value shows the reason why the log space isn't being reused and continues to grow.
5.      For databases with the SIMPLE recovery model, the log files will usually reclaim space on their own.
6.      For databases with FULL or BULK_LOGGED recovery model, even if everything is working normally, only creating a transaction log backup will allow the log space to be reused. A full backup by itself is not sufficient.



Check Log Space
1.      Open SQL Management Studio and connect to the server.
2.      Click New Query.
3.      Type the following SQL Command and click Execute:
DBCC SQLPERF(logspace)
4.      Find the database with the log file that is continuing to grow and note the Log Size (MB) and Log Space Used (%) columns (example below):
DB Name
Log Size (MB)
Log Space Used (%)
Status
DB1
1999.992
5.894042
0
DB2
624.6875
7.902545
0
DB3
626.3672
2.313767
0
DB4
459.1172
4.275252
0

             
             
             
             
             
             
            If the Log Space Used (%) is very low then you can potentially reclaim a lot of disk space. If it is high, then you must address the issue noted in the log_reuse_wait_desc column earlier.
             
Check Log File Usage
1.      Open SQL Management Studio and connect to the server.
2.      Click New Query.
3.      Type the following SQL command and click Execute:
DBCC LOGINFO(database_name)
4.      Look at the Status column, and find the last row that has a status of 2 (example below):
FileId
FileSize
StartOffset
FSeqNo
Status
Parity
CreateLSN
2
95485952
7831027712
627
0
64
624000016876300007
2
95485952
7926513664
628
2
64
624000016876300007
2
95485952
8021999616
0
0
0
624000016876300007
2
95485952
8117485568
0
0
0
624000016876300007

            Each log file (.ldf) is internally split into Virtual Log Files (VLF) that are managed by SQL. It starts at the beginning of the file and writes new to new VLFs sequentially (FSeqNo), cleaning up old VLF blocks as it goes. A status of 2 means the VLF is in use, a status of 0 means the VLF is marked as free and can be reused.
5.      You can only shrink a Log File if there are free VLF blocks at the end. If the Latest VLF (Highest FSeqNo) is near the end of the log file, then it will wrap back around to the start of the file as long as there are VLF marked as free (status 0) at the beginning. Then a transaction log backup needs to be made to free up the old VLF blocks at the end of the file.
6.      If the Log file has a high used percentage and waiting for a log backup to be made, you must schedule Full (once daily) and Transaction log (every hour) backups in a Maintenance Plan (you can also run a manual transaction log backup, but it will not prevent the log file size issue from reoccurring).

No comments:

Post a Comment