Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

How to determine SQL Server database transaction log usage

MSSQLTips author Greg Robidoux By:   |   Read Comments (11)   |   Related Tips: 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 2005 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 2005 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:

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 WITH NO_LOG
--or
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.

One thing to note, if you do run BACKUP LOG...WITH NO_LOG you will need to run another full backup, otherwise SQL Server will just reuse the space in the transaction log because there is no way to restore the next transaction log backup since you did not do a real transaction log backup and therefore the settings in the log file were reset.  Also, if you don't have a full backup of your database the space in the transaction log also gets reused. This is because there is no full backup to restore first and therefore you can not issue a transaction log restore.

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 are going to use BACKUP LOG...WITH NO_LOG you should just look at changing your database to the Simple recovery model
  • If you do need to shrink your transaction log file take a look at DBCC SHRINKFILE.
  • Here are some other tips regarding transaction log space.


Last Update: 4/18/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, April 05, 2012 - 8:10:50 AM - Bhupendra Read The Tip

awosome.. it really help.. great  article


Wednesday, April 18, 2012 - 9:39:16 PM - Sri Read The Tip

Thank you for this post Greg!

Very straightforward and clear and not to mention helpful!

Sri


Tuesday, May 29, 2012 - 6:54:19 PM - Yana Gudev Read The Tip

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

Yana


Thursday, October 04, 2012 - 2:49:58 PM - Chiranjib Saha Roy Read The Tip

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 


Thursday, October 04, 2012 - 5:24:27 PM - Greg Robidoux Read The Tip

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


Saturday, January 05, 2013 - 4:50:08 AM - hassan Read The Tip

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?


Monday, June 10, 2013 - 11:29:37 PM - Special Read The Tip

What are reason of Model database become full ?


Friday, August 09, 2013 - 6:44:45 AM - sara Read The Tip

 

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


Friday, August 09, 2013 - 11:12:04 AM - Greg Robidoux Read The Tip

@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/


Thursday, August 22, 2013 - 1:42:12 PM - Ed Read The Tip

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?


Monday, September 16, 2013 - 2:08:07 PM - Greg Robidoux Read The Tip

@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/



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.