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

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?

From stackoverflow Adam N

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

  Adam N : Not my choice. The DBA says that statically sized transaction logs are more efficient.

  Tom H. : I'm more of a SQL developer than strictly a DBA, but the performance should only be affected if the log file is not correctly sized and has to grow itself too often. The actual act of growing eats up some performance, but the autogrow should mostly be for unforeseen emergencies anyway.

  Adam N : DBCC SQLPERF needs extra permissions on the server, which is why I went with the sys.database files answer. DBCC SQLPerf does have the advantage of giving you all of the databases at the same time...
  From Eric Z Beard

• 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!

  From Tom H.

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

  From Diego