DBCC CheckDB failed with error “database snapshot could not be created and the database or table could not be locked”

Error: 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] (Error 50000).  The step failed.

Msg 1823, Level 16, State 2, Line 1

A database snapshot cannot be created because it failed to start.

Msg 7928, Level 16, State 1, Line 1

The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

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.

Msg 5128, Level 17, State 1, Line 1

Write to sparse file ‘E:\MSSQL\DATA\master.mdf:MSSQL_DBCC9’ failed due to lack of disk space.

Issue: SQL Server itself creates DB snapshot from SQL Server 2005 onwards at time of executing DBCC checkDB. DBCC CheckDB is extensive activity which take lock on database and can cause blocking and slowness.

Microsoft write DBCC CheckDB code again in SQL Server 2005, which create DB snapshot while executing DBCC CheckDB and use snapshot during checkDB.

Due to disk space issue, SQL Server is not able to create Database snapshot and job failed with mentioned error. We have clear some space in drive and job completed successfully in next run.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp