Unable to run Database Integrity checks when database open

[ian-cope]

Whenever we run the DatabaseIntegrityCheck on the User Databases, if there is a connection to a database, the job will fail.
I run the job at the weekend, which minimises the number of open connections.
This has been happening, as far as I'm aware, since I started using Ola's brilliant solution. This happens on our older 2016 SQL Server and also on our new SQL 2019 server,
If I close any active connections, then the job runs fine.

Output from log where it fails:

Database: [xxxx] [SQLSTATE 01000]
State: ONLINE [SQLSTATE 01000]
Standby: No [SQLSTATE 01000]
Updateability: READ_WRITE [SQLSTATE 01000]
User access: MULTI_USER [SQLSTATE 01000]
Recovery model: SIMPLE [SQLSTATE 01000]
Is accessible: Yes [SQLSTATE 01000]
[SQLSTATE 01000]
Date and time: 2020-11-30 08:57:34 [SQLSTATE 01000]
Database context: [master] [SQLSTATE 01000]
Command: DBCC CHECKDB ([xxxx]) WITH NO_INFOMSGS, ALL_ERRORMSGS [SQLSTATE 01000]
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 Microsoft Knowledge Base article 928518 for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details. [SQLSTATE 42000]
Outcome: Failed [SQLSTATE 01000]
Duration: 00:00:20 [SQLSTATE 01000]
Date and time: 2020-11-30 08:57:54 [SQLSTATE 01000]
[SQLSTATE 01000]

[olahallengren]

This is an issue with the CHECKDB command. It is not related to my stored procedure.

https://support.microsoft.com/en-us/help/928518/error-message-when-you-run-any-of-the-dbcc-check-commands-in-sql-serve

---

This problem occurs if the following conditions are true:

• At least one other connection is using the database against which you run the DBCC CHECK command.
• The database contains at least one file group that is marked as read-only.

---

Does this database have a read-only file group?

[ian-cope]

Hi Ola Not that I’m aware of. I always assumed that for most sql servers there would always be active connections and that the checkdb is able to cope with this by using snapshots. It is clear that if I can close an active connections, the integrity checks work. Is this going to be one for MS support? Cheers Ian

[olahallengren]

I would advise you to open a case with Microsoft Support.

[ian-cope]

Ok thanks Ian Ian Cope IT Manager (ECSG) | IT Systems Admin (Health Sciences) Tel: 01904 321886 – for the foreseeable future please ring mobile Mob: 0787 231 2250 [image: ECSG] Epidemiology and Cancer Statistics Group ECSG – Department of Health Sciences, Faculty of Science, University of York <http://www.york.ac.uk/healthsciences/research/ecsg/> Department of Health Sciences Department of Health Sciences, Faculty of Science, University of York <https://www.york.ac.uk/healthsciences/> Email Disclaimer <https://www.york.ac.uk/about/legal-statements/email-disclaimer/> *From:* Ola Hallengren <[email protected]> *Sent:* 01 December 2020 11:30 *To:* olahallengren/sql-server-maintenance-solution < [email protected]> *Cc:* ian-cope <[email protected]>; Author <[email protected]> *Subject:* Re: [olahallengren/sql-server-maintenance-solution] Unable to run Database Integrity checks when database open (#463) I would advise you to open a case with Microsoft Support. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#463 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKDI3NWANUIOJUIOKOXDUEDSSTHTXANCNFSM4UHJOZ2Q> .