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.
Best Answer
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