question

VijayKumar768 avatar image
0 Votes"
VijayKumar768 asked ·

DBCC check DB on SQL Server 2016

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-generalsql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi VijayKumar768, We have not received a reply from you. Did the answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
CarrinWu-msft avatar image
0 Votes"
CarrinWu-msft answered ·

Hi @VijayKumar768,

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.


· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered ·

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.

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered ·

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.

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered ·

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.

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.