Solved

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

Posted on 2010-09-17
13
1,199 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
  • 7
  • 6
13 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> 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
Comment Utility
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
Comment Utility
>> 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
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Welcome..
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
Comment Utility
>> 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now