WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Tuesday, March 15, 2011

Shrinkage: How low do I need to go to fix VLF Fragmentation?

VLF(virtual log file) fragmentation has been written about quite a bit in the past few years, in which I learned about it from SQLSkills .  One thing I did was to add a field on the end of the result set that shows you a running total of megabytes for each VLF.  This features is nice if you need to know how far to shrink your log to achieve a desired number of VLFs.  Note: There is a great deal out there about why shrinking is bad, etc please be sure to tackle the root issue before spending lots of time fixing this.  Small growth rates on the log file, bad backup schedules, etc are usually to blame.

DECLARE @beginlsn numeric(22,0);
DECLARE @increment bigINT;
    SET @increment = 1;

IF (OBJECT_ID('tempdb..#vlf') IS NOT NULL)

CREATE TABLE #vlf(fileid int, filesize bigint, startoffset numeric(13,0), fseqno int, status smallint, parity int, createlsn numeric(22,0));

DECLARE @db varchar(128);
SET @db =  DB_NAME()

EXEC ('DBCC LOGINFO('''+ @db +''')');

    ADD growIncrement INT,row_id INT IDENTITY(1,1);

SET @beginlsn = (SELECT TOP 1 createlsn
                 FROM #vlf
                 WHERE growIncrement is null
                 ORDER BY startoffset);

-- Set growth increment (shows how many vlfs were created in a single growth)
WHILE EXISTS(SELECT 1 FROM #vlf WHERE growIncrement is null)
    UPDATE #vlf SET growIncrement = @increment WHERE createlsn = @beginlsn;
    SET @beginlsn = (SELECT TOP 1 createlsn FROM #vlf WHERE growIncrement is null order by startoffset)
    SET @increment = @increment + 1;

SELECT  fileid,fseqno,status,parity,growincrement,row_id as VLF_Number
    , (SELECT (SUM(filesize) / 1048576) FROM #vlf v2 WHERE v2.row_id <= v.row_id) as RunningTotalInMB
FROM #vlf v
ORDER BY row_id desc

No comments:

Post a Comment