DBCC: The database could not be exclusively locked to perform the operation

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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