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.