Diagnosing and rectifying SQL Transaction Log Growth

Diagnosing and rectifying SQL Transaction Log Growth

Ever wonder why your transaction logs can grow out of control? First place to check is whether the Database is set to Full or Simple mode.   Unless you are running in Simple Mode, backups need to be taken at least hourly to reduce the accumulating Transaction Logs.

The database keeps its own sys log that can tell you what’s preventing Transaction Logs from being cleared; here’s a query to run:

SELECT TOP 1000 [name]
,[log_reuse_wait]
,[log_reuse_wait_desc]
,[is_date_correlation_on]
FROM [master].[sys].[databases]

First thing to try is to Shrink the database, assuming you are already in Simple Recovery Mode:

USE dbname
CHECKPOINT
--First parameter below is the fileno for the log file, often 2. Check the sys.database_files
--Second parameter is the target size in MB.
DBCC SHRINKFILE(2, 500)
DBCC SQLPERF(LOGSPACE)
DBCC LOGINFO

Logfile space is not automatically recovered.  The SQL to Truncate and recover the log space is to run the following against the LDF file:

DBCC SHRINKFILE
(
logical file_name
target_size in MB ], TRUNCATEONLY
)
WITH NO_INFOMSGS
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *