How to determine SQL Server database transaction log usage


By:   |   Updated: 2020-10-23   |   Comments (18)   |   Related: 1 | 2 | 3 | 4 | More > Database Administration


Problem

One crucial aspect of all databases is the transaction log.  The transaction log is used to write all transactions prior to committing the data to the data file.  In some circumstances the transaction logs can get quite large and not knowing what is in the transaction log or how much space is being used can become a problem.  So how to you determine how much of the transaction log is being used and what portions are being used?

Solution

In most databases, the transaction log is generally just one (ldf) file, but inside the overall transaction log is a series of virtual log files as depicted below.

SQL Server viritual log files

source (SQL Server Books Online)

The way the transaction log is used is that each virtual log file is written to and when the data is committed and a checkpoint occurs the space becomes useable again.  Although this does depend on your database recovery model, whether you are using replication and your backup processing.  If there are no additional virtual logs available, SQL Server will grow the transaction log, based on your database settings, to accommodate the additional space that is required. 

SQL Server vlfs

source (SQL Server Books Online)

The use of the file and the virtual logs all depends on how the database is used and other settings you have enabled in your database.  If you are publishing data from this database or if the database is set to the Full or Bulk-Logged recovery mode, this will also affect whether the process loops back to the beginning of the file, if it uses the next available virtual log or it if needs to grow the transaction log and create additional virtual logs.

Get space used by transaction logs using DBCC SQLPERF(logspace)

One command that is extremely helpful in understanding how much of the transaction log is being used is DBCC SQLPERF(logspace).  This one command will give you details about the current size of all of your database transaction logs as well as the percent currently in use.  Running this command on a periodic basis will give you a good idea of how the transaction logs are being used and also give you an idea on how large they should really be.  This is a question that is often asked by a lot of people that use SQL Server and as you run this you will find out there is no perfect answer it all depends on a lot of criteria such as:

  • recovery model
  • size of the transactions
  • how large your tables are and therefore how much space is needed for index maintenance
  • how frequently you run transaction log backups
  • whether the database is published or not
  • etc.

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

DBCC SQLPERF(logspace)

This is sample output:

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:

sample output

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

BACKUP LOG DBUtil TO DISK = 'C:\Backup\DBUtil.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.

sample output

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 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 sample output:

dbcc opentran output

Now that you have an idea 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.

Next Steps
  • Make sure you are using the correct backup strategy based on your recovery model
  • If you need to shrink your transaction log file take a look at DBCC SHRINKFILE.
  • Here are some other tips regarding transaction log space.


Last Updated: 2020-10-23


get scripts

next tip button



About the author





Comments For This Article




Friday, October 23, 2020 - 3:56:14 PM - Greg Robidoux Back To Top (86691)
Thanks Konstantin. I just updated the article and remove the NO_LOG backup. Probably not a good idea to include to make sure someone doesn't do something didn't intend to do.

-Greg

Friday, October 23, 2020 - 11:24:12 AM - Konstantin Momchilov Back To Top (86689)
Nice post as usual just update this statement has not been in use since SQL 2005 BACKUP LOG DBUtil WITH NO_LOG those days backup to disk = 'NUL' is used

Wednesday, May 15, 2019 - 6:21:31 AM - Tamil Selvan Back To Top (80083)

What is the Advantage and Disadvantage for determine the SQL server Database Transaction Log Usage?

Thank You,

TamilSelvan


Wednesday, November 21, 2018 - 9:08:42 PM - michele salerno Back To Top (78305)

 Good afternoon,

Excellent article.  Question:

when I run dbcc loginfo, I get this line

recovery unit =0 

filed =2 

Filesize = 39594229760 

Startoffset = 10737418240 

FSeqNo = 225 

Status = 2 

Parity = 128 

CreateLSN = 70000062929500020

 

how do I find the process that is hlding this log in mssql ?

In advance, thank you for your help in this matter

regards,

Michele


Thursday, June 09, 2016 - 10:56:02 AM - bala Back To Top (41652)

 

I've the same issue but its not resolved. If transaction log grows bigger,

My Scenario -

1. Daily rebuild index from maintainance plan.

2. Taking backup(.BAK)&Transactionlog (.trn) both from same maintainance plan. 

 

Now i've changed  my maintainance plans, created two maintainance plans, one for backup(.BAK) and and other for transactionlog(.trn),

it resolved my issue..

 

Thanks

Bala


Wednesday, January 27, 2016 - 5:26:14 PM - Rose Back To Top (40517)

 

 Thanks. Very explanatory...


Tuesday, January 20, 2015 - 6:41:18 AM - pl80 Back To Top (35995)

This doesn't answer one key question: "which transaction has caused a high log usage?".  DBCC OPENTRAN is not the answer, as the longest running transaction might not be the one using up the most log space.  Is there a way to pinpoint the transaction responsible?  Tx.


Monday, September 16, 2013 - 2:08:07 PM - Greg Robidoux Back To Top (26821)

@Ed - take a look a this tip about VLFs and performance: http://www.mssqltips.com/sqlservertip/2107/performance-issue-with-large-number-of-virtual-log-files-in-sql-server-transaction-log/


Thursday, August 22, 2013 - 1:42:12 PM - Ed Back To Top (26438)

Hi, I understand how db engine uses tran log, but I do not understand how would increased number of VLFs could affect transaction log backups or recovery performance?


Friday, August 09, 2013 - 11:12:04 AM - Greg Robidoux Back To Top (26186)

@Sara - it is hard to say because it depends on how large the rows are that are being inserted, so the transaction log may need a lot of space even if you have only have one table.

A general rule is to make the transaction log around 20-25% of the data file, but again this all depends on the database and how large the largest table is that you have in your database.

Run the DBCC SQLPERF(logspace) command to see how much space is actually being used and also compare this to your overall database size.

You should also check the RECOVERY MODEL of the database.  It coudl be in FULL recovery and if you are not doing transaction log backups this will just keep growing.  See this for more info: http://www.mssqltips.com/sqlservertutorial/2/sql-server-recovery-models/


Friday, August 09, 2013 - 6:44:45 AM - sara Back To Top (26178)

 

Log Size 1008.242MB for a db witch is having a single table with approximately 30 individual insertion is reasonable?


Monday, June 10, 2013 - 11:29:37 PM - Special Back To Top (25382)

What are reason of Model database become full ?


Saturday, January 05, 2013 - 4:50:08 AM - hassan Back To Top (21269)

Issue with Query Execution speed!

Is it cause database execution to become slow?

when I execute "dbcc loginfo" for my database I have 307 rows! Does it mean somthing is wrong? and cause to reduce execution speed?


Thursday, October 04, 2012 - 5:24:27 PM - Greg Robidoux Back To Top (19793)

@Chiranjib Saha Roy - based on your scenario if transaction logs were missing and you only had full backups and transaction log backups the best you will be able to do is to restore your full backup and all of the transaction logs that you have there were created in sequence.

So if you have 12 transaction log backups and #6,7,8 are missing the best you an do is restore #1,2,3,4,5 and recover only the data up to that point.

Hope this answers your question.


Thursday, October 04, 2012 - 2:49:58 PM - Chiranjib Saha Roy Back To Top (19791)

Issue with restoration

Suppose my database has configured with full recovery model . Backed up full backup @ 1 AM every night and after every create tranaction log backup 30 mins

Scenario:- Database has been corrupted at 10:01 AM.  DBA saw that last transaction log backup was there before that 7 transaction logs were missing. how DBA restore the database with no data lose.

any clarification require, please feel free to contact 


Tuesday, May 29, 2012 - 6:54:19 PM - Yana Gudev Back To Top (17709)

Thanks!  How do I go about checking how much space does individual transaction takes in a log?

Yana


Wednesday, April 18, 2012 - 9:39:16 PM - Sri Back To Top (16995)

Thank you for this post Greg!

Very straightforward and clear and not to mention helpful!

Sri


Thursday, April 05, 2012 - 8:10:50 AM - Bhupendra Back To Top (16788)

awosome.. it really help.. great  article



download





Recommended Reading

Monitor Your SQL Server Virtual Log Files with Policy Based Management

Performance Issue with large number of Virtual Log Files in SQL Server Transaction Log

Check SQL Server Virtual Log Files Using PowerShell

How to rename a SQL Server database

Move SQL Server transaction log files to a different location via TSQL and SSMS








get free sql tips
agree to terms