Wednesday, December 26, 2012

How to Fix DBCC CHECKDB Error: Msg 5030, Level 16, State 12, Line 1

Have you ever received such error messages after running following DBCC commands


The full error message description is:

“ 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. “

In this case, DBCC CHECKDB command gets failed & show above error message. There are many reasons behind this error message. Some are: 

Causes behind this error:

  • When you run the DBCC CHECKDB command on the database, at that time another connection is using the same database.  
  • If the SQL Server database has file group that is marked as read-only, this error can occur. The reason behind it is that when DBCC CHECK check database consistency then it create and use an internal database snapshot. In this case, If a read-only file group exists then the internal database snapshot is not created & this error occur.

Solution to resolve this issue: 

1.First you should create a database snapshot for the database on which you want to perform the checks. 
2.After that run the DBCC CHECK command against the database snapshot.
3.Drop the database snapshot after the DBCC CHECK command is completed.

If it does not fix your problem, but after using above steps at least DBCC CHECKDB will finish and you'll have a better idea where the real problem exist.

No comments:

Post a Comment