Search code examples
sql-servertransaction-log

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

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.

Solution

  • 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