DBCC CHECKDB with readonly filegroups

I can’t believe this. I’ve got a large (2+ TB) database that I want to do daily integrity checks on. Because it is so big, I was using PHYSICAL_ONLY. Here’s the command:

DBCC CHECKDB(BigDB) WITH PHYSICAL_ONLY, TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS

I very quickly get this error:

Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

After a quick google search I found this KB article that explains the issue. Evidently DBCC CHECKDB doesn’t work against databases that contain filegroups that are flagged as read-only. Wow.

The recommend work around is to take a snapshot of the database, check the snapshot, and then drop the snapshot. This is what I thought DBCC CHECKDB does internally anyways! Oh well, this isn’t the strangest thing I’ve ever heard of coming from Microsoft.

Here’s what I came up with as a workaround:

DECLARE
@SnapName NVARCHAR(128)
,@SQL NVARCHAR(4000)

BEGIN TRY
SET @SQL = 'DBCC CHECKDB(' + @SnapName + ') WITH PHYSICAL_ONLY, TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS'
EXEC(@SQL)
SET @SQL = 'DROP DATABASE [' + @SnapName + ']'
EXEC(@SQL)
END TRY
BEGIN CATCH
SET @SQL = 'DROP DATABASE [' + @SnapName + ']'
EXEC(@SQL)
RAISERROR('DBCC CHECKDB failed on BigDB. Panic.',16,2)
END CATCH


Strange stuff.