DBCC CHECKDB runs longer

pdsqsql 391 Reputation points
2023-01-30T22:24:38.86+00:00

Hello,

We have Sql Server 2012 and we are running Ola's "DatabaseIntegrityCheck" job daily during off peak hours early in the morning, job runs fine but sometimes currently runs longer in our test server but production server no issue.

Server and Database settings are same in both Test and Production.

System going to be obsoleted so it's not updated with the latest patch.

We are having issue in both System databases and User Databases

This server had few databases but database are small and Total DBs size is less then 2 GB.

I checked TEMPDB and is not full.

Server having Disk Space also almost 95% Free space available.

When I checked WaitStat and it shows "RESOURCE_SEMAPHORE" for one of the user Database and Master database with command "DBCC TABLE CHECK" as stat shows 'SUSPENDED'.

I have standard MAX Memory setup to 80% out of 8 GB.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,692 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2023-01-30T22:26:55.74+00:00

    If it is going to be "obsoleted", I guess this just one more kick the butt to get that action going and upgrade the databases that needs to be upgraded.

  2. Seeya Xi-MSFT 16,436 Reputation points
    2023-01-31T03:20:38.1366667+00:00

    Hi pdsqsql,

    Please refer to this document: sys.dm_os_wait_stats (Transact-SQL)

    User's image

    You can run the sp_who command to get a list of all the current users, sessions and processes. You can then run the KILL command on any spid that is blocking others.

    Best regards,

    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments
  3. pdsqsql 391 Reputation points
    2023-01-31T23:47:07.07+00:00

    Seeya,

    Thank you very much for your detailed response.

    I already checked SP_WHO2 and there are no SPID session is blocking.

    I have also killed some inactive sessions.

    Not sure is it Memory issue or something else?

    I have also run some Memory related query:
  4. pdsqsql 391 Reputation points
    2023-02-08T04:14:01.28+00:00

    Seeya,

    I restarted the server so will wait till next time if you issue comes back.

    Thanks for all your help and everyone for your inputs.

    0 comments