There could be a beast lurking in your log files, robbing you of performance, and you might not even know its name. But fear not - I'll give you a weapon to kill the beast.
If you've never heard of "virtual log files" (VLFs), read these articles: they'll well-written, short, and extremely informative. You owe it to yourself to read the originals, but I'll sum them up briefly here.
VLFs: A Quick Overview
What we think of as a log file is actually a bunch of interconnected of disk space chunks called VLFs. A log file can consist of a few, or hundreds, or even thousands of VLFs, and a new one is created every time the log file needs to expand. In a perfect world, you could create your log file to be the biggest it would ever need to be; since the log file would never need to expand, there would be no new VLFs created. Problem solved.
Unfortunately, if you're reading this, you don't live in a perfect world. Very likely, you're responsible for databases that have too many VLFs, and having too many VLFs is bad, especially for performance. So, your first step is to check your databases' log files to see how many VLFs they have. Then, if they have too many VLFs, you can fix the problem with a few simple commands. Fortunately for you, I've created a stored procedure that does both steps, so you can spend your time on more interesting things.
(How many VLFs is "too many"? It depends. In this blog, our bias is toward providing tools to get things done; I leave theoretical questions to people far more qualified. I picked "50" because Kimberly said so, and that's good enough for me.)
The Weapon
The script below will create a stored procedure that will examine and, if necessary, fix, the VLF problem in all the databases in your instance. Usually, you'll only need to run this once in a very long while, but again, it depends. Note the "@ExecuteFix" argument - setting this flag to zero allows you to see what T-SQL code would be executed; setting it to one actually executes that T-SQL.
-- Drop if it already exists. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_FixExcessiveLogFileVlfs]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ------------------------------------------------------------------------------- -- Display, logs, and fixes all databases with more than 50 Virtual Log Files. -- -- See: http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/ -- See: http://sqlskills.com/blogs/kimberly/post/8-steps-to-better-transaction-log-throughput.aspx -- See: http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx -- -- Created 2012-04-28 - Larry Leonard - http://SqlSoundings.blogspot.com/ ------------------------------------------------------------------------------- CREATE PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs] @ExecuteFix INT = 0 AS BEGIN SET NOCOUNT ON -- Set up temp tables. IF OBJECT_ID('tempdb..#stage') IS NOT NULL DROP TABLE #stage CREATE TABLE #stage ( FileID INT , FileSizeBytes BIGINT , StartOffset BIGINT , FSeqNo BIGINT , [Status] BIGINT , Parity BIGINT , CreateLSN NUMERIC(38) ) IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results CREATE TABLE #results ( DatabaseName sysname , LogFileName sysname , VlfCount INT , LogFileSizeMB INT ) -- Gather the log file information into the temp tables. DECLARE @Sql NVARCHAR(MAX) SET @Sql = 'USE [?] ' + '' + 'INSERT INTO #stage ' + ' EXEC sp_executesql N''DBCC LOGINFO ([?])'' ' + '' + 'INSERT INTO #results ' + ' SELECT DB_NAME(), MIN(FILE_NAME(FileID)), COUNT(*), SUM(FileSizeBytes) / 1024 / 1024 ' + ' FROM #stage ' + ' ' + 'TRUNCATE TABLE #stage ' EXEC sp_msforeachdb @Sql -- Log the results. DECLARE @DatabaseName sysname DECLARE @LogFileName sysname DECLARE @VlfCount INT DECLARE @LogFileSizeMB INT DECLARE cur CURSOR LOCAL FOR SELECT DatabaseName , VlfCount , LogFileSizeMB FROM #results ORDER BY VlfCount DESC OPEN cur FETCH NEXT FROM cur INTO @DatabaseName, @VlfCount, @LogFileSizeMB WHILE @@FETCH_STATUS = 0 BEGIN RAISERROR('Database: %25s - Virtual Log Files: %4d - Size: %5d MB', 10, 1, @DatabaseName, @VlfCount, @LogFileSizeMB) WITH NOWAIT, LOG FETCH NEXT FROM cur INTO @DatabaseName, @VlfCount, @LogFileSizeMB END CLOSE cur DEALLOCATE cur -- Display the results. RAISERROR(' ', 10, 1) WITH NOWAIT SELECT * FROM #results ORDER BY VlfCount DESC -- Fix the log files with too many VLFs. We add two MB to the size because -- ALTER DATABASE requires that we make the log larger. Adding one doesn't -- work, because of rounding issues when dividing, I think. DECLARE cur CURSOR LOCAL FOR SELECT DatabaseName , LogFileName , VlfCount , LogFileSizeMB FROM #results WHERE VlfCount > 50 ORDER BY VlfCount DESC OPEN cur FETCH NEXT FROM cur INTO @DatabaseName, @LogFileName, @VlfCount, @LogFileSizeMB WHILE @@FETCH_STATUS = 0 BEGIN SET @Sql = 'USE ' + @DatabaseName + CHAR(10) + ' ' + CHAR(10) + 'CHECKPOINT ' + CHAR(10) + ' ' + CHAR(10) + 'DBCC SHRINKFILE (' + @LogFileName + ', 0, TRUNCATEONLY) ' + CHAR(10) + ' ' + CHAR(10) + 'ALTER DATABASE ' + @DatabaseName + CHAR(10) + ' MODIFY FILE ' + CHAR(10) + '( ' + CHAR(10) + ' NAME = ' + @LogFileName + CHAR(10) + ' , SIZE = ' + CAST(@LogFileSizeMB + 2 AS NVARCHAR) + ' MB ' + CHAR(10) + ') ' + CHAR(10) IF @ExecuteFix = 0 BEGIN RAISERROR('-- Proposed T-SQL code for database %20s: Log File: %10s', 10, 1, @DatabaseName, @LogFilename) WITH NOWAIT, LOG RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT, LOG END ELSE BEGIN RAISERROR('Processing database %20s: Log File: %10s', 10, 1, @DatabaseName, @LogFilename) WITH NOWAIT, LOG EXEC sp_executesql @Sql END FETCH NEXT FROM cur INTO @DatabaseName, @LogFileName, @VlfCount, @LogFileSizeMB END CLOSE cur DEALLOCATE cur -- Done. IF OBJECT_ID('tempdb..#stage') IS NOT NULL DROP TABLE #stage IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results END /* Testbed. EXEC dbo.dba_FixExcessiveLogFileVlfs @ExecuteFix = 0 */ GO
