Transaction Log files, Part 2 – Using Contig v1.55 to defrag files

I was dorking with Contig for checking fragmentation of SQL Server log files, and it seems to work… and not work at the same time, if that is possible. Here’s the skinny on Contig v1.55, if you haven’t seen this little tool, downloadable from Microsoft:

There are a number of NT disk defraggers on the market, including Winternals Defrag Manager. These tools are useful for performing a general defragmentation of disks, but while most files are defragmented on drives processed by these utilities, some files may not be. In addition, it is difficult to ensure that particular files that are frequently used are defragmented - they may remain fragmented for reasons that are specific to the defragmentation algorithms used by the defragging product that has been applied. Finally, even if all files have been defragmented, subsequent changes to critical files could cause them to become fragmented. Only by running an entire defrag operation can one hope that they might be defragmented again.

Contig is a single-file defragmenter that attempts to make files contiguous on disk. Its perfect for quickly optimizing files that are continuously becoming fragmented, or that you want to ensure are in as few fragments as possible.

Check this – first I am going to pick a database log file, shrink it to remove any fragmentation (VLFs), confirm the fragmentation with Contig and then fragment the hell out of it.  Then, look at it with Contig, defrag with Contig, and then check it again via DBCC Loginfo.

DBCC SHRINKFILE (2,0)
GO

We know the file looks pretty good with only 4 VLFs, so let’s run Contig. It’s gold as well:

Let’s run the frag script to frag the hell out of the file (you like that one didn’t you?):

SET NOCOUNT ON
DECLARE @cntr int = 0
DECLARE @size varchar (20) = '3000'
DECLARE @filesize varchar (20) = '3000'
DECLARE @incr varchar (2) = 'KB'
DECLARE @str varchar (max)
WHILE @cntr < 200000
BEGIN
    SET @str = 'ALTER DATABASE [Test] MODIFY FILE (NAME = N''Test_Log'', SIZE = '+@size 

             + @incr+')'
    EXEC (@str)
    SET @cntr+=1000
    SET @size = cast(CAST(@filesize as int) + @cntr     as varchar (20))    
END
GO 

Now run DBCC Loginfo again. It’s fragmented with 730 virtual log files (snippet here only):

Use Contig to see what’s up with the file – something is, because it’s showing a lot of fragmentation:

Just for the fun of it, I’m going to run the –v option to try and use the tool to defrag the log file.  From this screenshot, it looks like it did something:

But when I run DBCC Loginfo again, the VLFs are still inside of the file.  I’ll need to run the shrink file script from the beginning of this blog to clear out the VLFs.  So what have we accomplished?  Good question. My guess is that this tool appears to actually have moved the file to another block on the disk; it’s not fragmented across the disk, however, we can see that it is still fragmented inside of the log file.

In part 3, I’ll continue…have some more geeking with fragmentation inside of transaction log files.  I’ve got a real cool one up next.  Hell yeah!

Thanks for reading

Lee Everest

-------------------------

Are we beating a dead horse with all of this transaction log stuff? 

http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx