Database Integrity Check jobs running for ever in Sql 2012

  • pdsqsql

    Old Hand

    Points: 356

    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

  • Sue_H

    SSC Guru

    Points: 90779

    Did you check to see if the process is blocked by anything or if there are any waits when this runs forever?

    Sue

  • pdsqsql

    Old Hand

    Points: 356

    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.

  • Grant Fritchey

    SSC Guru

    Points: 396715

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • pdsqsql

    Old Hand

    Points: 356

    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.

  • Grant Fritchey

    SSC Guru

    Points: 396715

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • pdsqsql

    Old Hand

    Points: 356

    Thanks Grant for your help!

Viewing 7 posts - 1 through 7 (of 7 total)

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