Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-09-17
13
Medium Priority
?
1,288 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

963 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