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