Monitor: How to check transaction log space details for SQL Serverdatabases.

There are two ways to get the details of transaction log space in SQL Server,

1) Using DBCC command as below. By default this will give log space information for all the databases.

DBCC SQLPERF(LOGSPACE)
GO

Sample Output:

2) Using DMV - sys.dm_os_performance_counters. With this we can query log details specific to the databases that we need. You can use this for SQL Server versions 2005 or later.

SELECT instance_name AS db_name, 
       [Data File(s) Size (KB)] AS [data_file_size_KB], 
       [LOG File(s) Size (KB)] AS [log_file_size_KB], 
       [Log File(s) Used Size (KB)] AS [log_file_used_size_KB], 
       [Percent Log Used] AS [percent_log_used_%] 
FROM 
( 
   SELECT * 
   FROM sys.dm_os_performance_counters 
   WHERE counter_name IN 
   ( 
       'Data File(s) Size (KB)', 
       'Log File(s) Size (KB)', 
       'Log File(s) Used Size (KB)', 
       'Percent Log Used' 
   ) 
     ---AND instance_name != '_Total' /*uncomment this section and comment next line if you want details for all databases*/
  AND instance_name IN ('master','msdb','tempdb')
) AS tbl_source
PIVOT 
( 
   MAX(cntr_value) 
   FOR counter_name IN 
   ( 
       [Data File(s) Size (KB)], 
       [LOG File(s) Size (KB)], 
       [Log File(s) Used Size (KB)], 
       [Percent Log Used] 
   ) 
) AS tbl_pivot 

Sample Output: