January 27, 2020 at 7:04 pm
We have Database Integrity Check jobs running every early morning during off hours into sql server 2012 specially for Share point databases.
It's running currently for last 3 days as normally it's finishes in minutes.
We are using Ola scripts for our Database Maintenance Solution.
I didn't see any errors in logs and also nothing like DB corruption issue.
Could you please guide how to troubleshoot to check what's going on?
Thanks
January 27, 2020 at 7:28 pm
Did you check to see if the process is blocked by anything or if there are any waits when this runs forever?
Sue
January 28, 2020 at 12:48 pm
Thanks Sue.
Yes, I have already checked and nothing blocking or no any other running that can impact, even databases are also small.
It's a Sharepoint DB.
January 28, 2020 at 1:36 pm
If it has gone from minutes to days, that is highly abnormal. I'd suggest stopping that process. Disable it for the moment. Then, I would run each of the check commands within CHECKDB independently on the database in order to try understand where things are going wrong. For minutes to days, you may be dealing with corruption of some kind.
If you don't want to run this on production, and I would sure avoid it if possible, restore the database to a non-production server and run the commands there (a successfully restored database will carry any corruption with it). If the restore fails because of corruption, well, there's another clue as to the problem.
Other things to look at apart from blocking, wait statistics. What is it waiting on? CPU, IO, how are these things behaving in general? Something is up. Time to break everything you do down into discrete steps in order to identify it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2020 at 3:03 pm
Thanks Grant.
we are also running DBCC Check so doesn't looks like any corruption but having restoring on another server will be good idea.
It's waiting on RESOURCE_SEMAPHORE for DBCC TABLE CHECK.
CPU and IO shows high on only for this DBCC query.
January 28, 2020 at 3:29 pm
Sounds like it's CPU bound then. One or more of the processes is hung on CPU. Again, break down what you're doing and determine which process is stuck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2020 at 4:12 pm
Thanks Grant for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply