How to Run DBCC CHECKDB in SQL Server

DBCC CHECKDB is the command that tells you whether a SQL Server database is healthy. Run it on a schedule and it catches corruption early, while you still have good backups to fall back on. Skip it and the first sign of trouble is often a failed query in production.

This guide shows the exact way to run it, the options worth knowing, how to check every database at once and how to read what it gives back. It also covers the safe order to follow if it ever reports a problem.

What does DBCC CHECKDB check?

DBCC CHECKDB checks both the physical and the logical health of everything in a database. Under one command it runs three checks together: it checks how disk space is allocated, it checks the system catalog and it checks every table and index for consistency. That is why it is the one integrity command most DBAs lean on.

It finds the problem. It does not fix anything unless you add a repair option, which is a separate and risky step covered later. For a fuller look at what corruption looks like and how to catch it early, see our guide on checking database corruption in SQL Server.

How to run DBCC CHECKDB in SSMS

The command runs from a query window. There is no wizard to click through, just a short statement you execute against the database you want to check.

DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

To check the database you are already connected to, you can run DBCC CHECKDB on its own with no name. If the check is taking far longer than you expect, our guide on DBCC CHECKDB taking a long time covers why and how to speed it up.

The options you should know

The options change how thorough the check is and how it runs. These are the ones worth keeping in mind. Microsoft lists the full set in the DBCC CHECKDB reference.

How to run it on all databases at once

Checking one database at a time does not scale. This short cursor runs CHECKDB across every online user database on the instance. Drop the database_id filter if you want the system databases included too.

DECLARE @db SYSNAME;
DECLARE db_cursor CURSOR FOR
  SELECT name FROM sys.databases
  WHERE state_desc = 'ONLINE' AND database_id > 4;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
  DBCC CHECKDB (@db) WITH NO_INFOMSGS, ALL_ERRORMSGS;
  FETCH NEXT FROM db_cursor INTO @db;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;

How to schedule DBCC CHECKDB

A check only helps if it runs on its own, before trouble shows up. Put it on a schedule so you are not relying on memory. There are two easy ways.

The first is a SQL Server Agent job. Paste the script above into a job step, set it to run weekly during a quiet window, then add a job alert so you hear about a failure. The second is a Maintenance Plan with the Check Database Integrity task, which runs CHECKDB for you with a few clicks. Either way, the goal is the same: a regular, hands-off check with an alert when something is wrong.

How to read the results

The result lives in the messages pane, not a pass or fail light. A healthy database ends with a line that reports zero allocation errors and zero consistency errors. That line is what you want to see.

If there are problems, CHECKDB lists each one, names the object and the page it sits on, then ends with the smallest repair level that would clear the errors. That repair line is a description of the damage, not an instruction to run it. So a clean line means you are done, while any error count means it is time to act, in the careful order below.

What to do if it finds errors

Finding errors turns this from a check into a recovery job. The order you follow here decides whether you keep your data or lose part of it.

When the database is badly corrupt, will not come online or has no usable backup, a recovery tool reads the MDF and NDF files directly and pulls the data out, including objects a repair would have deleted. The SQL Database Recovery Software does this on a copy of the file, so the original stays untouched.

Download Now
Purchase Now

Going deeper: repair levels and last-resort options

Most guides stop at “run it and read the output.” The part that decides whether you keep your data is what happens after an error, so this section goes there. It is more technical, so it sits at the end.

REPAIR_REBUILD vs REPAIR_ALLOW_DATA_LOSS

The two repair levels are not two strengths of the same thing. They are different in kind. REPAIR_REBUILD fixes structural problems with no data loss, like a missing nonclustered index row or a broken index link, because that information can be rebuilt from data that is still intact. REPAIR_ALLOW_DATA_LOSS is the destructive one. It clears the errors by deallocating the corrupt pages, rows and all, so the database comes back clean precisely because the bad data is gone. The level CHECKDB prints is the minimum needed to clear every error, not a promise that it is safe.

Always run a repair inside a transaction

Here is a habit that has saved data more than once. Wrap a repair in a transaction so you can see what it did before you accept it. Run BEGIN TRANSACTION, then the repair, then look at what it reported. If you are happy, COMMIT. If it deleted more than you can accept, ROLLBACK and the database is back as it was. The repair needs the database in single-user mode first.

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BEGIN TRANSACTION;
DBCC CHECKDB ('YourDatabase', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- review the output, then COMMIT TRANSACTION or ROLLBACK TRANSACTION
ALTER DATABASE YourDatabase SET MULTI_USER;

When the database will not even come online

Sometimes CHECKDB cannot run because the database is marked suspect and will not start. As a last resort you can force it into emergency mode, which lets CHECKDB read pages the engine has marked bad and attempt a repair. This can rebuild the transaction log, which may break transactional consistency, so it really is the final option after the data is saved elsewhere. Microsoft explains the path in its guide to troubleshooting consistency errors.

ALTER DATABASE YourDatabase SET EMERGENCY;
ALTER DATABASE YourDatabase SET SINGLE_USER;
DBCC CHECKDB ('YourDatabase', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
ALTER DATABASE YourDatabase SET MULTI_USER;

Read the error like a map

Every consistency error is a location, not just a complaint. CHECKDB names the object and the page, with the page shown as file and page number, like 1:24968. The object is given as an ID, which you turn into a table name with a quick lookup. That tells you exactly what is damaged and where, which is far more useful than the summary count alone.

SELECT OBJECT_NAME(<object_id>) AS TableName;

The safest path keeps the original file

Whatever route you take, protect the original. Before any repair, copy the MDF and NDF files somewhere safe and work only on the copy, so a repair that deletes too much is never the last word. For how a recovery tool reads that MDF directly, page by page, without SQL Server, see the technical section in our guide on DBCC CHECKDB taking a long time.

Final word

So running DBCC CHECKDB is the easy part. Open a query, run the command with NO_INFOMSGS and ALL_ERRORMSGS, check every database on a schedule and read the messages for that clean zero-errors line. The skill is in what you do next. If it ever reports a problem, restore a backup before you reach for a repair. Never work on your only copy of the file.

Before your next maintenance window, ask the question that matters most. If CHECKDB found corruption tomorrow, do you have a backup you trust? Or would that file be the only copy you have?