Can't CHECKDB if I've got READONLY filegroups.....
Posted on 2010-09-17
This is a good one.
Last night my maint failed on one of my dev boxes, during the CHECKDB step, with this error:
Command: DBCC CHECKDB ([dbname]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY
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.
As I am checking into it, I find this message in my error log many, many times:
The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following: 'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.
That message began yesterday evening at 10:00:35PM, immediately after the maintenance failure.
Been digging a bit, and 'I've just now realized this is due to one of the filegroups being READONLY. It's a HUGE partitioned db, keyed by fiscal business quarter, and I want to mark every filegroup but the non-current quarter as READONLY. Totally intentional, so that I can reduce the runtime and overhead required for my maintenance.
So yesterday I'm testing this out in the dev bed, hoping to do the same in production over the weekend.... and it all went to the toilet. I just set it back to READWRITE, ran CHECKDB successfully, and all those darned sp_syspolicy_events_reader messages are gone from my error log. (how the heck does this msg even pertain to the readonly filegroup?!)
Search as I might, I'm just not finding a means of ignoring the readonly filegroups in my CHECKDB.
Any input is truly appreciated.