One common, but often undetected issue that I see with SQL Server databases is a very high number of virtual log files (VLFs) inside the SQL Server transaction log file. This usually occurs because the default initial size and autogrowth increment are both far too small for most production databases. The default initial size is 3MB, with an autogrowth increment of 10 percent, which is ridiculous (see below).  You can modify those default settings for new databases by changing them in the Model system database, but I prefer to set those values explicitly when I create a new database.

If you have a database in Full recovery model, with almost any regular write activity, you will quickly fill up the 3MB transaction log file, causing a 10 percent autogrow to occur. Even in Simple recovery model, if you are writing to the transaction log fast enough that the Checkpoint process cannot keep up, you can easily fill up the log file and trigger that 10 percent autogrowth. In either case, you will then have a 3.3MB log file that will quickly fill up again, and grow again. Rinse and repeat, over and over. Each time the log file grows (whether it is through autogrowth or manually), you increase the number of VLFs in the log file. The number of new VLFs depends on how much the file grew by, not how big the entire transaction log is. What you want to avoid is lots of small growths. Instead, you want a small number of larger growths. Kimberly Tripp (blog | twitter) talks about how to choose an increment size for growing your log file here.

The problem with a high number of VLFs is that it can dramatically increase the amount of time it takes to restore or recover the database. Michelle Ufford (blog | twitter) relates a recent bad experience she had with this issue here. How big you should make your transaction log file depends on the amount of write activity (both for normal operation and for maintenance activities like index maintenance), and how often you do transaction log backups. How often you do transaction log backups depends on your business requirements for recovery point objective (RPO) and recovery time objective (RTO), and how large your transaction log file is (since you don’t want it to ever fill up and have to autogrow).

The script below shows some queries and commands you can use to detect and reduce the number of VLFs in your transaction log file.

-- Detecting and reducing VLFs in SQL Server 2008
-- Glenn Berry 
-- June 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
-- Switch to your database
USE ngmetadata;
GO
-- Check VLF Count for current database
DBCC LogInfo;
-- Check individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;
-- Step 1: Compressed backup of the transaction log (backup compression requires Enterprise Edition in SQL Server 2008)
BACKUP LOG [ngmetadata] TO  DISK = N'N:\SQLBackups\ngmetadataLogBackup.bak' WITH NOFORMAT, INIT,  
NAME = N'ngmetadata- Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;
GO
-- Step 2: Shrink the log file
DBCC SHRINKFILE (N'ngmetadata_log' , 0, TRUNCATEONLY);
GO
-- Check VLF Count for current database
DBCC LogInfo;
-- Step 3: Grow the log file back to the desired size, 
-- which depends on the amount of write activity 
-- and how often you do log backups
USE [master];
GO
ALTER DATABASE ngmetadata MODIFY FILE (NAME = N'ngmetadata_log', SIZE = 8GB);
GO
-- Switch back to your database
USE ngmetadata;
GO
-- Check VLF Count for current database after growing log file
DBCC LogInfo;