checkdb and snapshots

Question

checkdb and snapshots

inund8ed

Ten Centuries

Points: 1337
More actions
August 14, 2013 at 2:13 pm

#297705

Hi,
The system is SQL Server 2012 SP1 Enterprise Ed 64-bit 11.0.3349.0
Integrity Check has been failing on random user databases and on master and msdb, the error is:
[font="Courier New"]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.[/font]
The above error is generated for Integrity checks that were ran from a scheduled job or directly in Management Studio, and also when dbcc checkdb is ran WITH NO_INFOMSGS or WITH TABLOCK.
None of these messages are logged in SQL Server Error Log, and I did not find any other messages occurring at the same time in the log or in Event Viewer.
Tried creating a snapshot but failed, error message:
[font="Courier New"]Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5119, Level 16, State 1, Line 1
Cannot make the file "F:\MSSQL\Data\ReportServer_0814.ss" a sparse file. Make sure the file system supports sparse files.[/font]
If this is a permission issue, how come the integrity check is successful for some of the databases?
Any suggestions?
thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's in the error log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

Are the database that fail on different disks than the database for which DBCC works?

What is the file system for the F disk?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

inund8ed Ten Centuries Points: 1337 More actions · Answer 3

Gail,

The error log only shows the DBCC results for those databases that completed successfully.

The errors are captured in the maintenance plan history:

[font="Courier New"]Executing the query "DBCC CHECKDB(N'ReportServer') WITH NO_INFOMSGS

" failed with the following error: "The database could not be exclusively locked to perform the operation.

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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.[/font]

inund8ed Ten Centuries Points: 1337 More actions · Answer 4

Erland,

All data files (including those dbs that have dbcc run successfully against it) reside on the same disk drive, file system is NTFS.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Create a snapshot manually, check any messages in the error log.

Do you have any disk filter drivers installed?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

inund8ed Ten Centuries Points: 1337 More actions · Answer 6

I have tried creating a snapshot but it failed, error message:

[font="Courier New"]Msg 1823, Level 16, State 2, Line 1

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

Msg 5119, Level 16, State 1, Line 1

Cannot make the file "F:\MSSQL\Data\ReportServer_0814.ss" a sparse file. Make sure the file system supports sparse files.[/font]

I did see this Paul Randal link for file system drivers:

http://www.sqlskills.com/blogs/paul/search-engine-qa-14-beware-3rd-party-file-system-drivers-with-dbcc-checkdb/

we'll check that, however all databases reside on the same location so I would have assumed that if it was a driver issue then dbcc would not work on any of the databases.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Are there any relevant messages in the error log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Pieter Vanhove Right there with Babe Points: 733 More actions · Answer 8

Hi,

Did you actually find a solution for this problem?

I have the same issue.

DBCC CHECKDB is failing with the same error on msdb and master but not on model db.

All data files of those DB's are in the same folder, so I don't think this could be a permission issue.

I'm working on SQL Server 2012 Standard Edition Version 11.0.3373.0

Regards

Pieter

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 9

Check if Autoclose property for your database is set to True.

If so bring database offline and online to resolve this issue.

If issue still exists, please check the link below, hopefully this helps:

http://blogs.msdn.com/b/karthick_pk/archive/2010/03/07/dbcc-checkdb-fails-the-database-could-not-be-checked-as-a-database-snapshot-could-not-be-created-and-the-database-or-table-could-not-be-locked.aspx

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537