Here’s an issue that I thought was a one-off but it just popped up again over the weekend so I want to publicize it.
DBA runs a DBCC CHECKDB and gets output like the following:
Msg 5172, Level 16, State 15, Line 1 The header for file 'E:\Data\namechanged.mdf:MSSQL_DBCC14' is not a valid database file header. The PageAudit property is incorrect. Msg 5120, Level 16, State 9, Line 1 Unable to open the physical file "E:\Data\namechanged.mdf:MSSQL_DBCC14". Operating system error 0: "(null)". Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start. Msg 7928, Level 16, State 1, Line 1 The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline. etc
Wow! Looks like something’s seriously wrong with that database. Until you find that this happens on *all* the databases on the server. And on all the other servers in the data center too. What on earth’s going on? I saw a similar issue once before, back in 2004 or 2005 while customers were testing pre-RTM IDW builds of SQL Server 2005. After running a test and doing some investigation for me, the person who just hit this issue over the weekend confirmed my hypothesis that he’d hit the problem I saw a few years ago.
The problem is a 3rd-party file encryption solution. It installs a file-system filter driver that filters all reads and writes to the database files so it can perform the encryption/decryption. Unfortunately it doesn’t cope with NTFS alternate streams – which is how the internal database snapshots that DBCC CHECKDB uses are created – and so returns garbage for all reads from the database snapshot. The simple workaround is to create your own database snapshot (whose constituent files will exist in their own right, rather than being alternate streams of the source database’s files) and then run DBCC CHECKDB against that.
Now – I don’t want to cry ‘wolf’ here, but be careful of attributing massive corruption problems to the real IO subsystem if there’s any kind of file-system filter driver installed.
[Edit 6/19/14: From SQL Server 2014 the database snapshot is not hidden and does not use NTFS alternate streams.]