Alter Ego

So the other day I'm doing some light reading from Paul Randal about DBCC CHECKDB when I learn that, beginning with SQL 2005, CHECKDB actually performs its checks on an internal database snapshot and not the "real" database.  That was a totally unexpected bit of knowledge, so it really caught my eye.  It's great to have somebody that loves to share these things with the world.  Thanks, Paul!

A little more reading revealed that the internal database snapshot is implemented as an alternate stream of the live database file.  And also that I have no control over where it's created, so it can cause a nearly full drive to fill up during a CHECKDB (the original reason for his original article).

I had never heard of an alternate stream before, but I did find a couple pretty good explanations, that I won't cover here, other than to mention that it's a feature of NTFS and allows multiple data streams to be stored within a single file (the second link has downloadable source-code for utilities that manipulate alternate data streams, if you really want to get down in the weeds).

I wanted to see one of these alternate streams in action myself, and discovered that you can view a file's alternate streams by using the /R switch of the DIR command.  Here is DBCC CHECKDB in action, complete with a peek at the alternate steam in the file system.

I'm using the following command to provide directory listings for the  AdventureWorks database:

declare @cmd sysname

set @cmd = 'dir /R "C:\\AdventureWorks_Data*"'

exec master..xp_cmdshell @cmd

This shows the AdventureWorks_Data.mdf file:

Now run dbcc checkdb('AdventureWorks') and, while that is still running, kick off the DIR command from a separate SSMS window.  This shows the AdventureWorks_Data.mdf file and the alternate stream that is used for the internal database snapshot:

Notice that the DIR command only reported a single file in the summary.  The alternate data stream is completely invisible to most DOS style commands, and you'll never see one in Windows Explorer.

I didn't really expect to do so much digging after a little reading about DBCC CHECKDB, but it turns out there was a lot to learn, and I couldn't resist diving into the mysterious internal database snapshot and its alternate data stream.

Enjoy!