Thursday, November 21, 2013

VLF Fragmentation Analysis

While doing a little analysis of VLF Fragmentation, I found one database that had some trouble. Specifically, it had CDC enabled and the log reader agent had stopped a while ago. So, in short, the log had been growing unchecked for quite some time. After fixing the underlying problem, I decided to shrink the log. In order to help me find out where in the ring the log was, I came up with a little script.

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
CREATE TABLE #t
    (
      [FileId] TINYINT ,
      [FileSize] BIGINT ,
      [StartOffset] BIGINT ,
      [FSeqNo] BIGINT ,
      [Status] TINYINT ,
      [Parity] TINYINT ,
      [CreateLSN] NUMERIC(25, 0)
    );
INSERT INTO #t EXEC('dbcc loginfo');
WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY [StartOffset]) -
        ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [StartOffset]) AS [r]
    FROM #t
)
SELECT MIN([StartOffset]) AS [RangeStart]
    , MAX([StartOffset]) AS [RangeEnd]
    , MAX([StartOffset]) - MIN([StartOffset]) AS [RangeSize]
    , COUNT(1) AS [VLFCount]
    , CASE MAX([Status])
        WHEN 0 THEN 'Free'
        WHEN 2 THEN 'In Use'
        ELSE NULL
    END
FROM cte
GROUP BY [r]
ORDER BY MIN([StartOffset])
This will output the in use and inactive ranges. In most cases, this will be an "in use sandwich". That is, you'll have a bunch of inactive log, some active, and then more inactive. The second bit of inactive is what you would get rid of if you did a shrink operation on the log file right now. Also, feel free to use "in use sandwich". ☺

No comments:

Post a Comment