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

I used your code but, there was an error converting to an int. "Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int." So wherever there was an "*8" I changed it to *8.0 and the code works perfectly.

SELECT (size * 8.0)/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.0)/1024.0                  END AS max_size_in_mb   FROM YOURDBNAMEHERE.sys.database_files  WHERE data_space_id                            = 0