Monitoring Databases  Next» Next»

Lesson 4 Monitor databases with dbcc
Objective Use dbcc to monitor a database.

Monitor Databases Using DBCC in SQL Server 2025

Does SQL Server still use DBCC to monitor a database? It does. DBCC, short for Database Console Commands, remains fully documented and fully supported in SQL Server 2025 (17.x) for checking a database's internal consistency, the same role it has played since long before this course's earlier lessons on Activity Monitor and Dynamic Management Views ever existed.

The next three lessons walk through eight different ways to use DBCC to monitor and maintain a SQL Server database, including:
  1. DBCC CHECKALLOC: checks the data pages allocated to tables and indexes
  2. DBCC CHECKCATALOG: verifies that SQL Server's own system metadata is internally consistent
  3. DBCC CHECKDB: checks allocation and structural integrity across every object in a database, in a single comprehensive pass
  4. DBCC CHECKFILEGROUP: validates the data in a single file group
  5. DBCC CHECKIDENT: validates and, if needed, corrects a table's identity column
  6. DBCC CHECKTABLE: checks the data pages allocated to a single table and its indexes
  7. DBCC SHOW_CONTIG: an older index fragmentation report, deprecated since SQL Server 2005 in favor of sys.dm_db_index_physical_stats
  8. DBCC DBREINDEX: an older way to rebuild a table's indexes, deprecated since SQL Server 2005 in favor of ALTER INDEX ... REBUILD
This lesson covers the first three: DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG. All three check consistency rather than performance; they tell you whether the data on disk is structurally sound, not whether a query runs fast. That distinction matters once you have already worked through DMVs, Activity Monitor, and Query Store earlier in this course: those tools answer "how is this database behaving," while DBCC answers a more basic question underneath that, "is this database's data actually intact."

DBCC CHECKDB: The Command to Run First

DBCC CHECKDB checks the allocation and structural integrity of every object in a database, and it already runs CHECKALLOC, CHECKCATALOG, and a per table CHECKTABLE as part of that single pass. None of those three narrower commands needs to be run on its own as a matter of routine; CHECKDB already covers them.

DBCC CHECKDB
    [ ( database_name | database_id | 0
        [ , NOINDEX
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    ) ]
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , EXTENDED_LOGICAL_CHECKS ]
            [ , NO_INFOMSGS ]
            [ , TABLOCK ]
            [ , ESTIMATEONLY ]
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]
            [ , MAXDOP = number_of_processors ]
        }
    ]
  • database_name | database_id | 0: the database to check. Omitted, or set to 0, checks the current database.
  • NOINDEX: skips nonclustered indexes on user tables, to cut execution time. System table indexes are always checked regardless of this setting.
  • REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, REPAIR_REBUILD: attempt a repair instead of only reporting. The database must be in single user mode first, and REPAIR_ALLOW_DATA_LOSS is the one option here that can actually discard corrupted data to get the database back online. Restoring from a verified backup is almost always the better choice when one exists; repair is the option of last resort.
  • ALL_ERRORMSGS: returns every error found, rather than a capped subset.
  • EXTENDED_LOGICAL_CHECKS: adds logical consistency checks on indexed views, XML indexes, and spatial indexes, on top of the physical checks CHECKDB always runs.
  • NO_INFOMSGS: suppresses informational messages, so output only appears when there is a genuine error.
  • TABLOCK: takes a table level lock instead of generating an internal database snapshot. That can be faster on a system under heavy load, at the cost of some concurrency, and it can cause CHECKDB to miss a small number of errors it would otherwise catch.
  • ESTIMATEONLY: reports the tempdb space the check would need, without actually running it.
  • PHYSICAL_ONLY: limits the check to the physical structure of pages, record headers, and allocation, skipping the more expensive logical checks. Considerably faster, and a common choice for a frequent check on a large production database, with a full CHECKDB run scheduled less often.
  • DATA_PURITY: checks that column values stay within the valid range for their data type. Mutually exclusive with PHYSICAL_ONLY.
  • MAXDOP = number_of_processors: caps the degree of parallelism the check itself uses, independent of the server's own configured max degree of parallelism setting.
DBCC CHECKDB is resource intensive on a large database, which is why it is typically scheduled for a maintenance window, often as a SQL Server Agent job, the same job scheduling tool covered earlier in this course. A common pattern is a light, frequent PHYSICAL_ONLY check scheduled nightly, with a full CHECKDB run scheduled weekly or monthly when the server is least busy. On a long running check, progress is visible through sys.dm_exec_requests, in the percent_complete column for the session running the command, the same DMV this course has already used to answer "what is this instance doing right now," rather than leaving you watching a blank console until the check finishes.

A trimmed run against this course's own SalesDB:

DBCC CHECKDB ('SalesDB') WITH NO_INFOMSGS;
DBCC results for 'SalesDB'.
Service Broker Msg Types analyzed: 14.
Service Broker Service Contracts analyzed: 6.
DBCC results for 'Orders'.
There are 48213 rows in 612 pages for object "Orders".
DBCC results for 'Customers'.
There are 9540 rows in 118 pages for object "Customers".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'SalesDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Zero errors on both counts, allocation and consistency, is the result you want on every run. Anything else needs investigating before it compounds: a single allocation error today can mean several related errors a month from now, once more data has been written around the damaged structure.

What Happens When DBCC Finds an Error

If any DBCC check produces an error, it means SQL Server either did not store some piece of data correctly, or has incorrectly assigned storage somewhere in the database. The three repair options shown in the syntax above, REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, and REPAIR_REBUILD, exist for exactly this situation, but they are not interchangeable, and not all of them do something on every command.

For DBCC CHECKDB, each option behaves differently:
  • REPAIR_ALLOW_DATA_LOSS performs the most aggressive repairs. It can deallocate corrupted pages entirely, which means the rows on them are lost, so it is a last resort, used after a backup restore has been ruled out, ideally wrapped in a user transaction so the repair itself can be rolled back if it makes things worse.
  • REPAIR_FAST is retained for backward compatibility and no longer performs any actual repair action on current versions of SQL Server.
  • REPAIR_REBUILD performs repairs that do not risk data loss, such as rebuilding an index, but it cannot fix every kind of error CHECKDB finds.
Any repair option requires the target database to be in single user mode first:

ALTER DATABASE SalesDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('SalesDB', REPAIR_REBUILD);
ALTER DATABASE SalesDB SET MULTI_USER;
Single user mode here means exactly what it sounds like: a database configured to allow only one connection at a time, which is why WITH ROLLBACK IMMEDIATE is usually included, to force out any other open connections rather than waiting for them to close on their own. That makes a repair disruptive by nature, one more reason a verified backup restore is generally the preferred first option whenever one is available, with an actual DBCC repair reserved for situations where no usable backup exists.

One practical detail worth knowing before relying on any of this: for a very large database, the full output of a CHECKDB run does not always make it back to the client session that started it, and the SQL Server error log is written as the check runs regardless. A connection that times out, or a client application that was never built to display the thousands of lines a full CHECKDB on a multi-terabyte database can produce, is not a reason to assume the check itself failed. Checking the error log directly, through SSMS or xp_readerrorlog, is the more reliable way to confirm what a long running check actually found.

DBCC CHECKALLOC: Allocation Only, and Rarely Needed on Its Own

DBCC CHECKALLOC checks that every page in the database is correctly assigned, nothing more. Because CHECKDB already runs this exact check as part of its own pass, running CHECKALLOC by itself is mainly useful for a quick, allocation focused check that skips CHECKDB's more expensive logical work, or for estimating tempdb space with ESTIMATEONLY before committing to a larger run.

DBCC CHECKALLOC
    [ ( database_name | database_id | 0
        [ , NOINDEX
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    ) ]
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , NO_INFOMSGS ]
            [ , TABLOCK ]
            [ , ESTIMATEONLY ]
        }
    ]
Two things about this syntax are easy to get wrong by analogy with CHECKDB, since the keywords look identical but do not all behave the same way here:
  • NOINDEX is retained only for backward compatibility and has no effect on CHECKALLOC specifically. There are no nonclustered indexes for it to skip, since CHECKALLOC only ever examines page allocation.
  • Of the three repair options, only REPAIR_ALLOW_DATA_LOSS actually does anything for CHECKALLOC. REPAIR_FAST is retained for backward compatibility and performs no repair action, and REPAIR_REBUILD does not apply to it at all.
CHECKALLOC cannot check tempdb, because tempdb cannot be placed into the internal database snapshot the check relies on for transactional consistency. A tempdb allocation problem is resolved by restarting the Database Engine service, which drops and recreates tempdb from scratch rather than repairing it in place.

A run against the same database, reporting space allocation for every table:

DBCC CHECKALLOC ('SalesDB') WITH NO_INFOMSGS;
DBCC results for 'SalesDB'.
Table 'Orders', Index ID 1: 612 pages used in 78 extents.
Table 'Customers', Index ID 1: 118 pages used in 16 extents.
Total number of extents = 94, used pages = 730, referenced pages = 730.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'SalesDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The report lists every table and index with its page and extent counts, which makes CHECKALLOC's output genuinely useful for a different purpose than error checking: a quick, human readable picture of how much space each table is actually using on disk, extent by extent, without writing a query against the system views to get the same number.

DBCC CHECKCATALOG: System Metadata Consistency

DBCC CHECKCATALOG is narrower still. It checks consistency within the database's own system metadata, the catalog information that describes every table, column, and object in the database, through views like sys.columns, sys.types, and sys.objects, rather than checking user data or page allocation at all. It is already included in a full CHECKDB run, the same way CHECKALLOC is.

DBCC CHECKCATALOG
    [ ( database_name | database_id | 0 ) ]
    [ WITH NO_INFOMSGS ]
DBCC CHECKCATALOG ('SalesDB');
If no errors are found, DBCC CHECKCATALOG returns no messages at all. It only ever reports a problem; like CHECKALLOC, it cannot fix one. A genuine metadata inconsistency it turns up generally means restoring from backup rather than repairing in place, since the catalog views it checks describe the structure of the database itself, not data that can simply be deleted and reinserted.

Who Can Run These Commands, and Where

None of the three commands in this lesson are available to an ordinary login by default. Running CHECKDB, CHECKALLOC, or CHECKCATALOG requires membership in the sysadmin fixed server role, or the db_owner fixed database role for the database being checked, the same two roles this course's earlier security related lessons already covered as the broadest levels of access SQL Server grants. That restriction makes sense given what a repair option can do: a command powerful enough to discard corrupted data and bring a database back online needs to stay well outside what a general application login can trigger, intentionally or otherwise.

All three commands also work the same way on Azure SQL Database and Azure SQL Managed Instance, not only on an on-premises or VM-hosted instance, so a script built around CHECKDB during development does not need to be rewritten before it runs against a cloud hosted database later. The one practical difference worth knowing is that a PaaS environment like Azure SQL Database manages its own underlying storage redundancy, so while running CHECKDB there is still a reasonable and supported practice, the platform is already doing a layer of integrity protection underneath it that an on-premises instance has to handle entirely on its own.

Ad Scripting and Automation for SQL Server

Comparing the Three Commands

With all three covered, it is worth seeing them side by side, since the overlap between them is exactly what makes CHECKDB the command to reach for first in routine practice.

CommandWhat It ChecksIncluded in CHECKDBCan Repair
CHECKDBAllocation and structural integrity, every objectN/A, this is the comprehensive checkYes, single user mode required
CHECKALLOCPage allocation onlyYesPartial, REPAIR_ALLOW_DATA_LOSS only
CHECKCATALOGSystem metadata and catalog consistencyYesNo, detection only

In practice that overlap means a well run maintenance plan rarely calls CHECKALLOC or CHECKCATALOG directly. They remain useful as narrower, faster diagnostic tools: CHECKALLOC when you specifically suspect an allocation problem and want to skip CHECKDB's full logical pass, CHECKCATALOG after a change that touches system metadata directly, such as a failed upgrade step, and both when you want a faster targeted answer than a full CHECKDB run would give you on a very large database.

DBCC Alongside the Rest of This Course's Monitoring Toolkit

It is worth placing DBCC in context against the other monitoring tools this course has already introduced. DMVs, Activity Monitor, Query Store, and Extended Events all answer questions about behavior and performance: what is running right now, which query got slower, where time is being spent waiting. DBCC answers a different kind of question entirely: is the data actually correct. A database can perform beautifully, fast queries, low wait times, a clean Activity Monitor dashboard, while still carrying a corrupted page nobody has noticed yet, because corruption does not necessarily announce itself as a performance problem until a query actually tries to read the damaged data and fails outright.

That is the reason DBCC checks belong on a schedule of their own, independent of whatever performance monitoring is already running. A nightly PHYSICAL_ONLY check and a weekly full CHECKDB, both run as SQL Server Agent jobs with failure notifications configured, catch structural problems on a predictable cadence rather than relying on a query failure to surface them after the fact, the same proactive posture this course has emphasized from its first lesson onward.

What's Ahead

The next lesson moves on to DBCC CHECKFILEGROUP and DBCC CHECKIDENT, followed by DBCC CHECKTABLE for a single table check narrower than anything covered here. Two further commands close out this module's DBCC coverage, DBCC SHOW_CONTIG and DBCC DBREINDEX, and both are worth a word of caution now rather than as a surprise later: both have been deprecated since SQL Server 2005, in favor of sys.dm_db_index_physical_stats and ALTER INDEX ... REBUILD respectively. They are still documented and still technically functional in SQL Server 2025, which is why this course covers them, but that lesson will lead with the modern replacement in each case and introduce the deprecated command mainly as syntax you may still encounter in an older script.

SEMrush Software 4 SEMrush Banner 4