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.

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