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.
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
CREATE PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs] @ExecuteFix INT = 0
AS
BEGIN
SET NOCOUNT ON
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
)
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
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
RAISERROR(' ', 10, 1) WITH NOWAIT
SELECT *
FROM #results
ORDER BY VlfCount DESC
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
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage
IF OBJECT_ID('tempdb..#results') IS NOT NULL
DROP TABLE #results
END
GO
