Database consistency check for 2 TB database taking 14 hours 20 mins

Dhaval Ambawade 0 Reputation points
2024-02-28T16:42:06.3033333+00:00

1)Database size is 2 TB 2)Largest Tables (only one large table) Total pages it has - 274190987 3)Execution for this single DB consistency check is 13 hours 38 minutes 23 seconds. ---most of the time we had to cancel the execution just to avoid conflicts with business times i e 8 AM. 4)Job starts at 5:30 PM So this job most of the time conflicts with business hours ie 8 AM 5)So, I am looking for some appropriate action so this could finish before the business start time & we would not require canceling this execution. Looking forward for some ideas to tackle this case. Thanks, in advance! Dhaval

Windows Server 2019
Windows Server 2019
A Microsoft server operating system that supports enterprise-level management updated to data storage.
3,458 questions
Azure SQL Database
Azure VMware Solution
Azure VMware Solution
An Azure service that runs native VMware workloads on Azure.
317 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
42 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2024-02-28T22:21:28.49+00:00

    Restore a backup of the database to a different environment and run DBCC there. This is something that quite a few sites do. Not only to off-load the DBCC from the production machine, but it also serves to verify the backup.

    It is worth mentioning that if DBCC takes longer time than usual, this is often a token of corruption, because DBCC performs a lot of extra action to analyse that corruption.