DBCC loginfo, VLFs and SQL2000 and earlier.

I was reading Paul Randal and Kimberly Tripp’s discussions about Virtual Log Files (VLFs), found here.

DBCC loginfo

I found that performing a log backup will cause the active VLF to ‘rotate’ to the first VLF, freeing up the rest of the VLFs for maximum shrinkage. Uh, well, you know what I mean. In SQL Server 2005 and above, it may take several BACKUP LOGs before SQL Server rotates the active VLF automatically. However, in older versions of SQL server (I tested this on SQL 7.0), repeated BACKUP LOGs don’t do it. To rotate the active VLF, you can run the script below a few times to fill up currently active VLF, causing the rotation. The script will fill one extent at a time (a 64k block of 8k pages) until the active VLF rotates (Status=2 means it is active). Once the rotation occurs, backup the log one last time and then a shrink file on the transaction log only. This will bring the size way down. When you are done, there should be 3 VLFs left; the smallest unit available. From there, grow the log to 8,000 creating 16 MORE VLFs, then grow again to 16,000MB which will add another 16 VLFs. This will bring your total VLF count to 35 (3 + 16 + 16) for a 16GB (more accurately, 16,000MB which is close to 16GB but not the exact same size) transaction log. NOTE: Don’t grow by an increment of exactly 1024MB (which is the same as 1GB, but is different than 1000MB) due to a Microsoft bug. When I get another chance, I’ll formalize some code and post it.