September 10, 2014 at 3:03 pm
Haven't seen much activity from my post on dba.stackexchange.com, thought I'd give it a shot here.
http://dba.stackexchange.com/questions/76029/checkdb-fails-on-msdb-and-master-system-databases
I get the following error within my maintenance plans:
Executing the query "DBCC CHECKDB(N'master') WITH NO_INFOMSGS " failed with the following error: "The database could not be exclusively locked to perform the operation. 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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I get the following when I run the code from the query analyzer:
USE [master]
GO
DBCC CHECKDB(N'master') WITH NO_INFOMSGS, 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.
I've searched high and low trying to find a fix, but no dice.
FACTS:
- SQL Server 2008 R2 Standard 64-bit (10.50.4000.0)
- Windows Server 2008 R2 64-bit w/SP1
- VMWare Virtual Machine
- Production server, so I can't reboot server/instance without internal change management process
- SQL Server Agent & Database Engine service accounts have full access to the folders and files where the MDFs and LDFs are housed
- Restarted SQL Server Agent via Config Mgr.
- SQL Server Agent has read permissions on the entire volume
- There are no read-only FGs
- DBs are not in Single-user mode, read-only, or in emergency mode
- Not running TABLOCK with DBCC command
- The DBs are on NTFS
The following are a few places I've read out of many, but these are the most applicable to my matter as I see it. I've performed the suggested "fixes" with no change in result or error message output from the SQL Server Error Logs.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
September 15, 2014 at 1:58 pm
Alright, posted this in twitter (#sqlhelp), linkedIn, and some other places....no one has an answer 🙁
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
September 16, 2014 at 11:09 am
You can really get around that error directly, as SQL has control of master.
Back up the master db and restore it to a different name.
RESTORE DATABASE master_dbcc_checkdb
FROM DISK = 'x:\full\path\to\master.BAK'
Then run the check on that db. That should give you a very good idea of the extent of the errors you're facing.
Btw, make backup copies of all the SQL error logs immediately, before they scroll off and you lose the data. Only the log where the error first occurred usually has much detail on the error(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 17, 2014 at 8:34 am
That is a cool trick that I didn't think of, thank you!
I'll use this for the time being to make sure I have good data, but I'm still concerned that DBCC won't run in the middle of my backup maintenance plan.
It appears that I might have to open a case with M$ to get this figured out 🙁
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply