TSQL: How do I get the size of the transaction log?

Question

How do I get the current size of the transaction log? How do I get the size limit?

I'd like to monitor this so I can determine how often I need to backup the transaction log.

I usually have a problem with the transaction log when I perform large operations.

Answer 1

Based on SQL Server 2005, try this

SELECT (size * 8)/1024.0 AS size_in_mb,
  CASE WHEN max_size = -1 THEN 9999999   -- Unlimited growth, so handle this how you want
   ELSE (max_size * 8) / 1024.0
        END AS max_size_in_mb
FROM <YourDB>.sys.database_files
WHERE data_space_id = 0   -- Log file

Change YourDB to your database name

For an overall of all database sizes try DBCC SQLPERF

DBCC SQLPERF (LOGSPACE)

This should work in SQL 2000/2005/2008

Answer 2

If you want to monitor it in real time, try Performance Monitor (perfmon) while you are doing those large operations.

Perfmon can be used in many different scenarios.

Find out more from Technet.

Answer 3

From SQL Server 2012, there is another DMV (Dynamic Management View) available - sys.dm_db_log_space_usage. It has the advantage of returning the transaction log size as opposed to the transaction log file size (which might include unused space). This will change as space is consumed and not just when the file grows.

select
    used_log_space_in_percent  
from
    sys.dm_db_log_space_usage;  

This view alone would support stopping prior to the point at which the log file grows.

It could be used in combination with the sys.database_files view and its max_size column to get a more granular value for 'percentage of maximum size that has already been consumed' than would be possible with the sys.database_files view alone.