How Much Longer Will CHECKDB Take?

You’ve manually started DBCC CHECKDB to verify the integrity of a database. It churns. And churns. You check the Messages tab on Management Studio. Nothing. So, you start to wonder: Just how much longer will CHECKDB take to complete? A couple of minutes? A couple of hours? Who knows?

How long will DBCC CHECKDB take?

That’s a reasonable question to ask. Unfortunately, there’s not a straightforward answer to give, other than “It depends.”

It depends. But on what?

Paul Randal, now with SQLSkills, was the guy who originally created CHECKDB when he was with Microsoft. In this post, Paul shares why it’s so difficult to say how long CHECKDB will run. Although the post is from 2007, it’s worth checking out.

The best guess for how long it’ll take to run this time is to look at how long it took last time. However, conditions may have changed since the last run. So that may not be a really good indication. However, it’s probably the best guess we can make.

To see how long it took to run the last time, look in the SQL Server Logs. You can use SQL Server Management Studio to open the log file and search the entries for the line you’re interested in.

You can also use the XP_readerrorlog procedure. Keep in mind that this widely known extended stored procedure is still technically undocumented and thus unsupported.

Given that caveat, if you’d like to know how long CHECKDB took to run against the ReportServer database last time, you can execute the following T-SQL.

EXECUTE xp_readerrorlog 1,1, N'DBCC CHECKDB (ReportServer)';

The first parameter indicates that I’m searching through the first error log file (zero is the current file). The second parameter tells the procedure to look at the SQL Server Error logs instead of the SQL Server Agent logs. The third parameter is the phrase I’m looking for in the log file.

Ask SQL Server for a DBCC percentage complete

So, now we know how long its taken in the past, what about this run? What can can learn about its progress?

Fortunately, there’s the sys.dm_exec_requests Dynamic Management View. It returns information about each request executing in the SQL instance. One of the columns is percent_complete. We can use that.

The script below can be used to return information about the DBCC CHECKDB progress.

SELECT 
    session_id AS [Session ID], 
    command AS [Command], 
    start_time AS [Start Time], 
    percent_complete AS [Percent Complete]
FROM 
    sys.dm_exec_requests 
WHERE 
    command LIKE 'DBCC%'

You can see the output below.

By the way, the sys.dm_exec_requests can be used for other purposes. For example, in Script: How Long Until My SQL Server Backup/Restore Completes?, I use it to estimate how much longer a SQL backup or restore will take.

Want to work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.