Solved

Can't CHECKDB if I've got READONLY filegroups.....

Posted on 2010-09-17
13
1,253 Views
Last Modified: 2012-05-10
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
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33701131
>> 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
 
LVL 17

Author Comment

by:dbaSQL
ID: 33701256
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33701302
>> 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 17

Author Comment

by:dbaSQL
ID: 33701327
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 33701349
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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 33701387
>> 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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33701397
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 33701512
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 33701611
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 33702188
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33703763
Welcome..
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 33704338
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33706402
>> 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question