February 25, 2016 at 7:05 am
Good day,
We currently run a Check DB Integrity at 4AM but lately its been taking over 10 minutes, locking up the Database for some time which results in the application not being available.
Any idea what I could do ? Do I need a daily integrity check? What's the best practice?
Thanks so much in advance..
February 25, 2016 at 7:35 am
Is anything running in SQL Server (like a Sql Server job) while the Integrity check is running? It is not designed to cause blocking as it does schema locks as part of its last steps - see https://www.mssqltips.com/sqlservertip/2399/minimize-performance-impact-of-sql-server-dbcc-checkdb/. One thing you could do, is restore your database to another server, like a test one, and run the integrity check there instead.
February 25, 2016 at 7:46 am
DBCC is a disk-intensive operation. I suspect that the database becomes unresponsive due to resource contention rather than "locking up".
Try investigating blocking (set up an extended events session for the blocked process report event) and wait stats during the dbcc job.
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply