How can I get the size of the Transaction Log in SQL 2005 programmatically?

Question

We're working with a fixed transaction log size on our databases, and I'd like to put together an application to monitor the log sizes so we can see when things are getting too tight and we need to grow the fixed trn log.

Is there any TSQL command that I can run which will tell me the current size of the transaction log, and the fixed limit of the transaction log?

Answer 1

A quick google search revealed this:

DBCC SQLPERF ( LOGSPACE )

Why aren't you using autogrowth on the transaction log? It seems like this would be a more reliable solution.

Answer 2

This is off the top of my head, so you might want to double-check the math...

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
     MyDB.sys.database_files
WHERE
     data_space_id = 0   -- Log file

There is more that you can get from that system view, such as the growth increment, whether or not the log is set to autogrow, and whether it is set to grow by a specific amount or by a percentage.

HTH!

Answer 3

If you really need to stick to a fixed size transaction log, I'd suggest to set it to a reasonable size, allowing some margin, and then do one of the following two:

• Set database recovery mode to SIMPLE, if you don't need point in time recovery. In simple words, it will allow transaction log to "self-recycle" the space.

OR

• If you must keep recovery mode to FULL, schedule a job which performs a backup of transaction log. This will free space in the transaction log, and also allow you to do point in time recovery, if needed.

Also, maybe you can find the following article useful: How to stop the transaction log of a SQL Server database from growing unexpectedly.