Freigeben über

Too Many Virtual Log Files (VLFs) Can Cause Slow Database Recovery

  • Artikel

The Transaction Log of a SQL Server database is composed of one or more physical files.  For each database, SQL Server only writes to one physical transaction log file at a time.  Although some firms choose to have more than one physical log file, the general guideline for SAP databases on SQL Server is to only use one physical file per database transaction log.  Ideally, transaction log files should be created at their desired size at installation rather than being allowed to grow slowly over time.  For SAP systems, sizing of transaction logs is approached in a rough, rule-of-thumb fashion.   Generally along these lines based on system size:

Small               20 – 30 GB

Medium          100 – 200 GB

Large               ~500 GB

Very Large      ~1 TB

 

Although compression delivers substantial space savings for SQL data files, this does not mean that the log files should be proportionately reduced.  Even for very small systems we recommend a 20-30 GB Transaction log file.

Internal to the physical file that SQL Server uses for the Transaction Log are structures known as Virtual Log Files (VLFs).  The sizing and number of VLFs inside of a transaction log can be affected by a number of factors.

SQL Server should not have an excessive number of Virtual Log Files (VLFs) inside the Transaction Log.  Having a large number of small VLFs can slow down the recovery process that a database goes through on startup or after restoring a backup.  The threshold for significantly impacting the recovery performance appears to be somewhere around ten thousand VLFs.  No symptoms are felt when there are a few thousand VLFs or less.  When there are around a hundred thousand VLFs, the symptoms become substantially noticed.

One can determine the number of VLFs in a particular database by seeing how many records are returned as a result of this DBCC command executed from within the context of the database:

DBCC LOGINFO

 

This excessive number of VLFs situation can arise from at least two different ways: Either from an initially small transaction log that was grown (manually or automatically) in very small increments, or from a problematic situation where large growth increments were configured but unexpectedly small Virtual Log Files were configured inside of the Transaction log.  Under either scenario it is possible to see over a hundred thousand VLFs.  The former situation is a Database Administration issue that should be attended to.   Standard SAP Installations should install a correctly sized Transaction Log for the database.  The latter situation is a newly identified issue that exists in all versions of the product and has now been substantially ameliorated in recent CUs for both SQL Server 2008 and 2008 R2.   See Microsoft KBA 2653893 for more information and redirection to the latest CUs.   Even so, if you have very large numbers of VLFs you will still need to correct the situation.

 

To correct the situation and prevent its recurrence:

  1. Backup the Transaction Log of the database
  2. Shrink the Transaction Log as much as possible using DBCC SHRINKFILE
  • Ideally do this in a maintenance window
  • Multiple SHRINKFILE executions might be called for to substantially shrink it

Expand the Transaction Log back to its target initial size

Set the Transaction Log growth size to be in units of 1 GB

Apply the latest CUs for SQL Server 2008 or 2008 R2

Of course, since this is a quality SAP environment, you know to make sure to run through these steps first on your DEV and QA systems before applying them to PRD.

 

Additional Information

For more information about VLFs and the architecture inside of a Transaction Log see the topic “Transaction Log Physical Architecture” in SQL Server Books Online (either local or online here)

 

DBCC SHRINKFILE documentation can be found here.

 

More information on Shrinking the Transaction Log can be found here.

 

You can monitor the size and available space in a Transaction Log by using the command:

DBCC SQLPERF(LOGSPACE)   {located online here}

 

SAP OSS Note 1671126 has similar information as well. 
With an SAP S, D, I, or C user account and after logging in, you can get to that note here:

https://service.sap.com/sap/support/notes/1671126

 

This issue is not planned to be addressed in any subsequent CUs for SQL Server 2005.   But the steps to correct the situation outlined above should work to sufficiently prevent the symptoms from occurring there as well.

Comments

  • Anonymous
    February 25, 2012
    How is the second one done or why does it happens ?
  1. initially small transaction log that was grown (manually or automatically) in very small increments
  2. problematic situation where large growth increments were configured but unexpectedly small Virtual Log Files were configured inside of the Transaction log.  

  • Anonymous
    March 21, 2012
    Nice post focusing on SAP. I actually had a customer SAP database where the transaction log has 1.2M VLFs, causing a +30h recovery just in the enumeration stage. Fixing it went down to about 2m recovery, and this was pre-update. I have a bunch of VLF related posts on my teams blog, if you care about this subject at blogs.msdn.com/.../vlf Cheers PL

  • Anonymous
    February 05, 2014
    The comment has been removed