[SQL] How to determine SQL Server database transaction log usage

 To run this command issue the following in a query window:

DBCC SQLPERF(logspace)

This is sample output

From here we can see the size of the transaction logs as well as how much space is being used.  The current log space used will tell you how much of the transaction log is being used.  If this percentage is high and the size of the log is quite big it is probably due to one of the items listed above. 

Getting information about SQL Server virtual logs using DBCC LOGINFO

The next command to look at is DBCC LOGINFO. This will give you information about your virtual logs inside your transaction log.  The primary thing to look at here is the Status column.  Since this file is written sequentially and then looped back to the beginning, you want to take a look at where the value of "2" is in the output.  This will tell you what portions of the log are in use and which are not in use Status = 0.  Another thing to keep an eye on is the FSeqNo column. This is the virtual log sequence number and the latest is the last log.  If you keep running this command as you are issuing transactions you will see these numbers keep changing.

To run this command issue the following in a query window:

DBCC LOGINFO

This is sample output:

If we now run a transaction log backup such as the following:

BACKUP LOG DBUtil TO DISK = 'C:\Backup\test.trn'

and then rerun the command you will see how the Status=2 has changed in the file. The last entry is still marked as in use, but the previous entries have been reset to 0.

Finding open SQL transactions using DBCC OPENTRAN

Another command to look at is DBCC OPENTRAN. This will show you if you have any open transactions in your transaction log that have not been completed or have not been committed.  These may be active transactions or transactions that for some reason never completed.  This can provide additional information as to why your transaction log is so big or why you may not be able to shrink the transaction log file.  This will show you both open transactions as well any un-replicated transactions if the database is published.

To run this command issue the following in a query window:

DBCC OPENTRAN

This is a sample output:

Now that you have an idea of how much of your transaction log is being used and what is being used you can start to make some decisions on how large the transaction log should be.  One thing you should try to do is find that optimum size in order to eliminate having to shrink and grow the transaction log on a constant basis.  As with all database and server activity, it is best to minimize the overhead as much as you can and this is one of those areas that you can somewhat manage by creating and maintaining the optimum transaction log size.

how big should my log file be?

For your log file is twice the size of the largest index in your database or 25% of the database size. Whichever is larger.

Why?

If the largest object in your database is larger than 25% of your database, you are likely running some type of maintenance. Index rebuilds require the size of the object being rebuilt in log space. Usually rule of thumb twice that space, in case you’re doing anything else while you’re doing that maintenance, like reports, dragging data to and purging data, whatever. If you’re only ever reorganizing the largest object, you may not need all that space.

Select your Database and run the below query to see what is your recommended log size. 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

WITH    log_size           AS ( SELECT TOP 1                         SCHEMA_NAME(t.schema_id) AS schema_name ,                         t.name AS table_name ,                         i.name ,                         p.rows AS row_count ,                         CAST(( SUM(CONVERT(BIGINT, a.total_pages)) * 8. ) / 1024. / 1024. AS DECIMAL(18,2)) AS index_total_space_gb ,                         ( SUM(CONVERT(BIGINT, a.total_pages)) * 8 ) / 1024 / 1024 * 2 AS [largest_index_times_two_(gb)] ,                         ( SELECT    ( SUM(CONVERT(BIGINT, mf.size)) * 8 ) / 1024 / 1024                           FROM      sys.master_files AS mf                           WHERE     mf.database_id = DB_ID() ) AS [database_size_(gb)] ,                         ( SELECT    CAST(( SUM(CONVERT(BIGINT, mf.size)) * 8 ) / 1024 / 1024 AS INT)                           FROM      sys.master_files AS mf                           WHERE     mf.database_id = DB_ID()                                     AND mf.type_desc = 'LOG' ) AS [current_log_size_(gb)] ,                         ( SELECT    CAST(( SUM(CONVERT(BIGINT, mf.size)) * 8 ) / 1024 / 1024 * .25 AS INT)                           FROM      sys.master_files AS mf                           WHERE     mf.database_id = DB_ID()                                     AND mf.type_desc = 'ROWS' ) AS [25%_of_database_(gb)]                FROM     sys.tables t                INNER JOIN sys.indexes i                ON       t.object_id = i.object_id                INNER JOIN sys.partitions p                ON       i.object_id = p.object_id                         AND i.index_id = p.index_id                INNER JOIN sys.allocation_units a                ON       p.partition_id = a.container_id                WHERE    t.is_ms_shipped = 0                GROUP BY SCHEMA_NAME(t.schema_id) ,                         t.name ,                         i.name ,                         p.rows                ORDER BY index_total_space_gb DESC)      SELECT ls.schema_name,             ls.table_name,             ls.name,             ls.row_count,             ls.index_total_space_gb,             ls.[largest_index_times_two_(gb)],             ls.[database_size_(gb)],             ls.[current_log_size_(gb)],             ls.[25%_of_database_(gb)] ,             CASE WHEN ls.[largest_index_times_two_(gb)] > ls.[25%_of_database_(gb)]                  THEN ls.[largest_index_times_two_(gb)]                  ELSE ls.[25%_of_database_(gb)]             END AS [maybe_this_is_a_good_log_size(gb)]      FROM   log_size AS ls OPTION  ( RECOMPILE );

And the below method is used when the log is not shrunk. 

DBCC SQLPERF(logspace) --check log size

--Shrink Log for Restored Database

ALTER DATABASE "put your DB name" SET RECOVERY SIMPLE

USE "put your DB name"

DBCC SHRINKFILE ("put your log name", "put the size you want")  

ALTER DATABASE "put your DB name" SET RECOVERY FULL

DBCC SQLPERF(logspace)  --check log size after shrink