One of the more common wait types for SQL Server is called WriteLog. In its most basic form it is a wait that is experienced as things are being written to the log file.
This wait could be an indication of a major performance problem dealing with log file fragmentation. You typically see some log file fragmentation after a growth event with the log. Within the LDF file there are files called Virtual Log Files (VLF), as the LDF file grows you typically see the number of VLF’s grow as well. An over whelming number of VLF’s cause your log file to become fragmented.
To identify how many VLF’s your log file has run the following query:
This query will return 1 row for each VLF.
If you are suspecting some log file fragmentation you can resolve a high number of VLF’s by doing the following:
1. Shrink your log file to an appropriate size based on the database and its activity.
2. Reconfigure your auto growth settings on the log file to an appropriate size based on how much activity is in the database.
Written by: Justin Figg a Sr. SQL Server DBA with over 10 years of IT experience with Windows OS, Windows Server and Microsoft SQL Server. Subscribe to receive notifications of newly posted blog entries.Support VitaminDBA.com