DBCC check DB on SQL Server 2016

Vijay Kumar 2,016 Reputation points
2021-02-22T23:29:10.363+00:00

Currently we are using hallengren for Database integrity (DBCC).

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@PhysicalOnly = 'Y',
@LogToTable = 'Y'

Which is working fine with no issue. taking 5 hours in prod envi. (Which is expected).

Is there any way i can reduce the executing time by dividing DBCC job or in someother way. so that i want only to run max 2 hrs.

Or any Microsft provided script which can improve?

This is SQL Server 2016 AlwaysOn (Primrary+Secondary)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,098 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,580 questions
{count} votes

Accepted answer
  1. CarrinWu-MSFT 6,866 Reputation points
    2021-02-23T06:33:26.887+00:00

    Hi @Vijay Kumar ,

    Firstly, good to know that there is no issue when the database integrity check has been finished. Is this database is very large? Because it takes 5 hours. If you have a very large database, please refer to CHECKDB (Part 6): Consistency checking options for a VLDB to get more information, see below:
    • Run a bi-weekly DBCC CHECKALLOC
    • Figure out your largest tables (by number of pages) and split the total number into 7 buckets, such that there are a roughly equal number of database pages in each bucket.
    • Take all the remaining tables in the database and divide them equally between the 7 buckets (using number of pages again)
    • On Sunday:

    • Run a DBCC CHECKALLOC
    • Run a DBCC CHECKCATALOG
    • Run a DBCC CHECKTABLE on each table in the first bucket
      • On Monday, Tuesday, Wednesday:
    • Run a DBCC CHECKTABLE on each table in the 2nd, 3rd, 4th buckets, respectively
      • On Thursday:
    • Run a DBCC CHECKALLOC
    • Run a DBCC CHECKTABLE on each table in the 5th bucket
      • On Friday and Saturday:
    • Run a DBCC CHECKTABLE on each table in the 6th and 7th buckets, respectively

    And also, you can use below SQLQuery to monitor the progress of DBCC:

    SELECT  session_id, request_id, percent_complete, estimated_completion_time ,  
    DATEADD(ms,estimated_completion_time,GETDATE()) AS EstimatedEndTime,   
     start_time, status, command    
     FROM sys.dm_exec_requests  
    WHERE command like '%DBCC%'  
    

    Best regards,
    Carrin


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Shashank Singh 6,246 Reputation points
    2021-02-23T08:30:44.157+00:00

    Is there any way i can reduce the executing time by dividing DBCC job or in some other way. so that i want only to run max 2 hrs.

    You are already running with parameter @physicalonly which itself does not do complete checks. It does only physical level checks not logical. So reducing the checks to 2 hours would ideally be not possible if you run complete checkdb.

    You can ofcourse can figure out the database which is important and give it a 2 hour window. Rest all can be run in separate windows. This what most people do out there.

    0 comments No comments

  2. Jeffrey Williams 1,891 Reputation points
    2021-02-23T20:14:29.86+00:00

    I am not sure that Ola's tools take into consideration the MAXDOP setting - which is now available for Enterprise Edition. If you have Enterprise Edition - and your server/database MAXDOP setting is not the default value of 0, then CHECKDB will run at the server/database specified setting.

    If you have Standard Edition - then CHECKDB always runs single-threaded.

    0 comments No comments

  3. tibor_karaszi@hotmail.com 4,306 Reputation points
    2021-02-24T08:57:21.013+00:00

    The most obvious thing IMO would be to check some databases on Monday, some other on Tuesday etc.

    Assumes of course that you have several databases and that not one of them is a huge database compared to the others.

    0 comments No comments