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". ☺
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])
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.