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