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.

Solution 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

Solution 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.

Comments

[Mes]

Thanks! Exactly what I've been looking for!

[Mes]

Why isn't size_in_mb set to 0 when I backup the transaction log?

[glagarto]

Dunno, after the backup of the transaction log, have you shurnk the database to free up the space? In the properties for the database what size does it show the transaction log to be?