CODEWITHSUNDEEP
sql-servert-sql
Adam Ness
Adam Ness

Reputation: 6273

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?

Upvotes: 22

Views: 80120

Answers (6)

James
James

Reputation: 3928

For SQL 2008 and later, FILEPROPERTY also exposes the amount of space used within a file and is much less complicated than all the other answers:

select [Name], physical_name [Path], CAST(size AS BIGINT)*8192 [TotalBytes], CAST(FILEPROPERTY(name,'SpaceUsed') AS BIGINT)*8192 [UsedBytes], (case when max_size<0 then -1 else CAST(max_size AS BIGINT)*8192 end) [MaxBytes]
from sys.database_files

Upvotes: 2

Martin Brown
Martin Brown

Reputation: 25310

Using sys.database_files only gives you the size of the log file and not the size of the log within it. This is not much use if your file is a fixed size anyway. DBCC SQLPERF ( LOGSPACE ) is a bit old school, but works well if you need to support older versions of SQL Server.

Instead you can use the dm_os_performance_counters table like this:

SELECT
    RTRIM(instance_name) [database], 
    cntr_value log_size_kb
FROM 
    sys.dm_os_performance_counters 
WHERE 
    object_name = 'SQLServer:Databases'
    AND counter_name = 'Log File(s) Used Size (KB)'
    AND instance_name <> '_Total'

Upvotes: 13

Myles Yamada
Myles Yamada

Reputation: 346

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

Upvotes: 33

Diego
Diego

Reputation: 7562

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.

Upvotes: 1

Tom H
Tom H

Reputation: 47464

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!

Upvotes: 7

Eric Z Beard
Eric Z Beard

Reputation: 38406

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.

Upvotes: 21

Related Questions