[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1323
  • Last Modified:

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_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.



0
dbaSQL
Asked:
dbaSQL
  • 7
  • 6
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> how the heck does this msg even pertain to the readonly filegroup?

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
0
 
dbaSQLAuthor Commented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dbaSQLAuthor Commented:
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
0
 
dbaSQLAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> i just need to ensure nobody is in the db, when it happens

Yes, exactly.

>> i wonder if i could do this effectively with a kill, rather than setting it to single user mode

Killing a connection or spid might make you lose some data and take resource intensive operations. Instead try scheduling your maintenance plan when no users would be connected to your database.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
0
 
dbaSQLAuthor Commented:
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?

0
 
dbaSQLAuthor Commented:
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
0
 
dbaSQLAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
0
 
dbaSQLAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now