Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I run DBCC CHECKDB ([MyDb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY

And after 20 seconds it fails with an error:

Msg 5030, Sev 16, State 12, Line 1 : The database could not be exclusively locked to perform the operation. [SQLSTATE 42000]

Msg 7926, Sev 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. [SQLSTATE 42000]

The database is 1TB, SQL Server 2005.

Why does DBCC need an exclusive database lock? How can I get the check done?

ADDED: DBCC runs successfully when I kill all connections to the database and then run checks. sp_who2 shows users accessing DB from web servers using .Net SQLClient provider

SQL Server Service runs under windows account which is the local Administrator.

share|improve this question
    
I ran into this same exact problem, deleting and recreating the maintenance plan fixed my issue....strange. –  Sean Perkins Aug 27 at 16:58

5 Answers 5

Most likely the SQL service account doesn't have permissions to create the snapshot files necessary.

On an active database its very unlikely CHECKDB will be able to get the X database lock necessary for the allocation checks to run, which is why I changed the lock timeout for it to be only 20 seconds (IIRC).

Thanks

share|improve this answer
    
DBCC runs successfully when I kill all connections to the database and then run checks. –  user46529 Mar 29 '11 at 19:43
1  
Awesome! You got an answer from a SQL god! :) –  Peter Schofield Mar 29 '11 at 20:24
    
I checked. SQL Server Service runs under windows account which is the local Administrator. It has something to do with user connections. As soon as I kill all connections to a database, DBCC runs fine. I am thinking of creating snapshot and running DBCC on it. –  user46529 Mar 30 '11 at 2:58

I am not sure the root cause that is stopping the snapshots being taken; but have you tried running DBCC CheckDB with Tablock option to see if it works? (Books online explicitly states tablock option "includes a short-term exclusive (X) lock on the database.") But atleast it doesn't take snapshots and DBCC CheckDB may succeed this time with this option. The other option is this isn't a critical system and you can afford down time; try changing it to single user mode and run the DBCC again OR restore it somewhere else and do the DBCC if you have test environment.

Your go to guy should be Paul Randal's Blog for all things CheckDB.

share|improve this answer
    
Running with Tablock gives me the same error. Because I still need a short-term exclusive (X) lock on the database. –  user46529 Mar 29 '11 at 15:52

I have written a detailed blog post in order to get rid of this error.

  1. Disconnect Connection. (To do so: File > Disconnect Object Explorer)
  2. Connect Connection. (To do so: File > Connect Object Explorer)
  3. Open "New Query" window and run following command: use master (Note: Above command will make your current database to master which is important before we run following sequence of commands.)
  4. Copy and paste following sequence of commands in Query window. Replace the word "MyDatabaseName" with Database name you are trying to get exclusive access.

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK AFTER 30

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH NO_WAIT

ALTER DATABASE MyDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE;

You are now done and you can now try the command or operation you were trying to perform earlier which was giving you Database could not be exclusively locked to perform the operation error.

share|improve this answer
up vote 0 down vote accepted

I ended up creating a snapshot and running DBCC on it.

share|improve this answer

Sometimes an issue arises when the hidden database snapshot runs out of space. Because it’s implemented using alternate streams of the existing data files, the database snapshot consumes space from the same location as the existing data files. If the database being checked has a heavy update workload, more and more pages are pushed into the database snapshot, causing it to grow. In a situation where the volumes hosting the database don’t have much space, this can mean the hidden database snapshot runs out of space and DBCC CHECKDB stops with an error. An example of this is shown here (the errors can vary depending on the exact point at which the database snapshot runs out of space):

Try this link

http://sqlism.blogspot.com/2014/10/a-database-snapshot-cannot-be-created.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.