What Is Filling Up The Transaction Log

From "SQL Server DMV Starter Pack" by Louis Davidson and Tim Ford.

Report unusual conditions that may be filling up your transaction log. It returns the recovery model, log reuse wait description, transaction log size, log space used, % of log used, compatibility level, and page verify option for each database on the current SQL Server instance.

For example, if your log reuse wait description is something unusual, such as ACTIVE_TRANSACTION, and your transaction log is 85% full, then some action is probably required.

SELECT db.[name] AS [Database Name] ,
 db.recovery_model_desc AS [Recovery Model] ,
 db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
 ls.cntr_value AS [Log Size (KB)] ,
 lu.cntr_value AS [Log Used (KB)] ,
 CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
 AS DECIMAL(18,2)) * 100 AS [Log Used %] ,
 db.[compatibility_level] AS [DB Compatibility Level] ,
 db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
 INNER JOIN sys.dm_os_performance_counters AS lu
 ON db.name = lu.instance_name
 INNER JOIN sys.dm_os_performance_counters AS ls
 ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
 AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;

Rate

Share

Share

Rate