Friday, December 4, 2015

Check Transaction Log space used in all databases

Super fast way to check the transaction log usage for all of your databases.  Pretty short and sweet, but it gives you a fast look at how much of your transaction logs are being used, per database. 

    /* quick check on log file usage */
    SELECT
       instance_name [Database],
       [LOG File(s) Size (KB)] [LogFileSizeKB],
       [Log File(s) Used Size (KB)] [LogFileSpaceUsedKB],
       [Percent Log Used] [%LogInUse]
    FROM
    (
       SELECT FROM sys.dm_os_performance_counters
       WHERE counter_name IN
         ('Log File(s) Size (KB)','Log File(s) Used Size (KB)','Percent Log Used')
       AND instance_name != '_Total'
     ) source pivot (
          MAX(cntr_value)
          FOR counter_name IN   
          ([LOG File(s) Size (KB)],[Log File(s) Used Size (KB)],[Percent Log Used])
     ) p2


This is the output from one of my instances:
















Pretty cool.  You can add data file sizes in there, too, or you can capture several other statistics like Lock Waits/sec, Lock Requests/sec, the number of active transactions.  Two of my favorites are the Log Growths and Log Shrinks. Monitoring and being aware of your log growths/shrinks is huge!  Run this, you will see those counters and more:

     SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
     FROM sys.dm_os_performance_counters;

Definitely take a look at this piece from MSDN regarding the other available counters:     https://msdn.microsoft.com/en-us/library/ms187743(v=sql.110).aspx

This is more information about using sys.dm_os_performance_counters to monitor system activity on a whole:   https://technet.microsoft.com/en-us/library/ms190382(v=sql.110).aspx