Can't CHECKDB if I've got READONLY filegroups.....
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.
Outcome: Failed
As I am checking into it, I find this message in my error log many, many times:
Message
The activated proc '[dbo].[sp_syspolicy_event
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
Search as I might, I'm just not finding a means of ignoring the readonly filegroups in my CHECKDB.
Any input is truly appreciated.
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.
Outcome: Failed
As I am checking into it, I find this message in my error log many, many times:
Message
The activated proc '[dbo].[sp_syspolicy_event
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
Search as I might, I'm just not finding a means of ignoring the readonly filegroups in my CHECKDB.
Any input is truly appreciated.
ASKER
hi rrjeqan17. that makes sense regarding the syspolicy reader messages.
I don't understand, however, how I will be able to run the CHECKDB, if one or more of my filegroups are readonly.
I don't understand, however, how I will be able to run the CHECKDB, if one or more of my filegroups are readonly.
>> however, how I will be able to run the CHECKDB, if one or more of my filegroups are readonly.
Yes, you can run DBCC CHECKDB with read only filegroups but you have to do that in Single user mode. As the error you have received states, "The database could not be exclusively locked to perform the operation." it seems like you have tried when database is in multi user mode.
Kindly confirm.
Yes, you can run DBCC CHECKDB with read only filegroups but you have to do that in Single user mode. As the error you have received states, "The database could not be exclusively locked to perform the operation." it seems like you have tried when database is in multi user mode.
Kindly confirm.
ASKER
interesting. i just set one of them back to READONLY, made sure nobody was in the db, and ran CHECKDB, successfully.
i'm unsure exactly what i've missed
i'm unsure exactly what i've missed
ASKER
aaah. ok. so changing my non-current quarter filegroups to readonly is doable, as is the maintenance execution of DBCC CHECKDB. i just need to ensure nobody is in the db, when it happens
i wonder if i could do this effectively with a kill, rather than setting it to single user mode
i wonder if i could do this effectively with a kill, rather than setting it to single user mode
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or this workaround might be of help to you..
http://www.sqlslayer.com/wp/2009/10/07/dbcc-checkdb-with-readonly-filegroups/
http://www.sqlslayer.com/wp/2009/10/07/dbcc-checkdb-with-readonly-filegroups/
ASKER
I actually saw that earlier, when I was surfing. I haven't really done much w/the snapshots before, rrjeqan.
The time of the maint is off hours, per se, but the connections are still there. the database is 24/6, Saturday is the only down day. I run checkdb, index optimization and backups every night.
The possibility of data coming in at that time is very small, but it is there. Do you think the snapshot is a better way around this?
The time of the maint is off hours, per se, but the connections are still there. the database is 24/6, Saturday is the only down day. I run checkdb, index optimization and backups every night.
The possibility of data coming in at that time is very small, but it is there. Do you think the snapshot is a better way around this?
ASKER
i just tried it again -- had one open connection in addition to the DBCC CHECKDB connection -- it failed, same error. i am going to try something with the kill, or single user. back shortly to let you know
ASKER
it works if i kill active connections, or place the db into single user mode before running the CHECKDB. i haven't yet tested the snapshot. (this database has 20 different filegroups, and even more files)
i will do some more research on the snapshot, but i think this will help me move forward for now.
thank you, rrjegan17
much appreciated
i will do some more research on the snapshot, but i think this will help me move forward for now.
thank you, rrjegan17
much appreciated
Welcome..
ASKER
it seems this may be a bug in v2005:
http://support.microsoft.com/kb/928518
but, i'm on v2008.
interesting
but, i've got it working. so i'm ok
http://support.microsoft.com/kb/928518
but, i'm on v2008.
interesting
but, i've got it working. so i'm ok
>> it seems this may be a bug in v2005:
Not exactly, this is how DBCC operates.
The link posted in my first comment (posting it again) is applicable for all versions of SQL Server.
http://msdn.microsoft.com/en-us/library/ms188796.aspx
Not exactly, this is how DBCC operates.
The link posted in my first comment (posting it again) is applicable for all versions of SQL Server.
http://msdn.microsoft.com/en-us/library/ms188796.aspx
Because DBCC CHECKDB tries to create a internal database snapshot, checks it and brings it back to consistent state.
Since it failed with the error message "The database could not be exclusively locked to perform the operation." with some users or operations performed on the databases, it tries to check the Online database which will use Table locks against all objects.
Trying to execute DBCC CHECKDB on a read-only filegroup would fail because of the below reason:(Excerpt from BOL)
"Trying to run DBCC CHECKALLOC, or the equivalent part of DBCC CHECKDB, by using the WITH TABLOCK option requires a database X lock. This database lock cannot be set on tempdb or master and will probably fail on all other databases."
Reference here:
http://msdn.microsoft.com/en-us/library/ms188796.aspx