0

Have been running the Ola Hallengren scripts on our new SQL Server 2016 Enterprise server for about 6 weeks or so now. The database Integrity script only works intermittently on some instances. Others are fine. I cannot find a cause for these failures.

enter image description here

The failure on the 9th failure above for example, seems to be fine for some databases, but then just stops:

enter image description here

I did run the checkdb manually against the database shown, and got the following initial message, but it appeared to complete ok:

DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.

Completed though with no issues:

enter image description here

I ran the output to a file, all databases except one succeeded.

Date and time: 2018-12-11 10:09:19 Command: DBCC CHECKDB ([xxxxxxx]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY Msg 5030, Level 16, State 12, Server XXXXX\XXXX, Line 1 The database could not be exclusively locked to perform the operation. Msg 7926, Level 16, State 1, Server XXXXX\XXXX, 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. Outcome: Failed Duration: 00:00:20 Date and time: 2018-12-11 10:09:39

Looking at the failures, it does fail on databases that have an open connection. For some I've managed to request users log off and all appears to work ok.

I've posted this on technet too, all suggestions appear superfluous.

Technet question

  • Are you by any chance using TABLOCK hint in checkdb ? – Shanky Jan 2 at 11:42
  • @Ian Cope, What is the database size which got failed in question? – Biju jose Jan 2 at 11:44
0

The message gives you the right info

The database could not be checked as a database snapshot could not be created and the database or table could not be locked.

Check that you have enough space in where the data file exists. If the data file is 10 GB make sure that you have enough space on the same drive to run the DBCC CHECKDB (Have at least 10 GB free space even though it will be less space used since usage of the sparse file but still keep the same for the safe side.) You can refer this article by Paul Randal here to understand more about DBCC CHECKDB

  • One of the failing integrity checks is on a database that is approx 10.5 GB. The database location has 34 GB free. There is no TabLock in Ola's script. – Ian Cope Jan 2 at 15:36
  • @IanCope can you post the ola script you are running for checkdb – Biju jose Jan 2 at 15:40
  • The script is also displayed in the technet question link above. It hasn't been modified in anyway. – Ian Cope Jan 3 at 7:45
  • I have created a new simple maintenance plan which runs the database integrity. As suspected, this would run successfully if databases not in use. I ran several checks, and each time queried activity monitor. Looking at the detailed logs too, I can definitely confirm that this task and the Ola Hallengren task runs the integrity check fine on all databases with no activity. Even databases that fail, once the activity clears, I can rerun and the integrity check works for those databases. Is this then as expected? For dbs that are accessed regularly how do I execute the integrity check? – Ian Cope Jan 3 at 9:04
  • Indeed, just noticed there was a brief interlude with no access for the integrity check on a instance user databases that has been failing for weeks, and worked fine. So suppose above question is the pertinent one. – Ian Cope Jan 3 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.