As a continuation of “Database Monitoring using DMV” series, this blog will cover how quickly you can address the log file space related issue. Below are the previous blogs which you may find useful in your everyday job.

• Disk Space Monitoring Using SQL Server DMV
• SQL Database File Wise Disk Space Monitoring

To address the log file space related issue, DBAs generally look at two different places;

• DBCC SQLPERF(LOGSPACE) to check the utilization of the log file by referring the column “Log space used %”
• sys.databases system view to check the column “Log Reuse Wait Description” to find out the reason – “what activity is causing space issue to the transaction log file?”

The below script will provide you both the information together along with some additional relevant columns

SELECT 
dbs.[name] AS [Database Name], 
CONVERT(DECIMAL(18,2), dopc1.cntr_value/1024.0) AS [Log Size (MB)], 
CONVERT(DECIMAL(18,2), dopc.cntr_value/1024.0) AS [Log Used (MB)],
CONVERT(DECIMAL(18,2), dopc1.cntr_value/1024.0) - CONVERT(DECIMAL(18,2), dopc.cntr_value/1024.0)[Log Free Space Left (MB)],
CAST(CAST(dopc.cntr_value AS FLOAT) / CAST(dopc1.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log space Used (%)], 
dbs.recovery_model_desc AS [Recovery Model], 
dbs.state_desc [Database State], 
dbs.log_reuse_wait_desc AS [Log Reuse Wait Description]
FROM sys.databases AS dbs WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS dopc  WITH (NOLOCK) ON dbs.name = dopc.instance_name
INNER JOIN sys.dm_os_performance_counters AS dopc1 WITH (NOLOCK) ON dbs.name = dopc1.instance_name
WHERE dopc.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND dopc1.counter_name LIKE N'Log File(s) Size (KB)%'
AND dopc1.cntr_value > 0 
order by 5 DESC OPTION (RECOMPILE)

The output of the above query:

Below are the columns definition to have a better understanding of them.

Database Name – It shows the database name
Log Size (MB) – The database Log file size in MB
Log Used (MB) – The database Log file used size in MB
Log Free Space Left (MB) – How much free space left inside the log file to be used
Log Space Used (%) – It indicates the database log file used %age
Recovery Model – It shows the database Recovery model like – Simple, Full, Bulk..etc
Database State –  The shows the database states like Online, Recovering, Suspected..etc
Log Reuse Wait Description – Description of reuse of transaction log space is currently waiting on one of the following; CHECKPOINT, LOG_BACKUP, REPLICATION, DATABASE_MIRRORING, ACTIVE_TRANSACTION…etc

Let me know if you find the query useful and want to add some additional information also in the query.I will publish it on the blog with due credit to you.