| Lesson 6 |
Monitoring with dbcc command, continued |
| Objective |
Perform miscellaneous database monitoring with dbcc. |
Perform Miscellaneous Database Monitoring with DBCC
Is DBCC still the right tool for ongoing database monitoring in SQL Server 2025? Mostly, no, and that is worth saying plainly before covering the last two commands in this module's roadmap. DBCC, short for Database Console Commands, remains fully supported, grouped into several categories including the miscellaneous and informational tasks this lesson closes out. But DBCC's real strength, as the previous two lessons established with CHECKDB, CHECKALLOC, CHECKCATALOG, and CHECKTABLE, is data integrity checking: confirming that a database, table, or filegroup is structurally sound. For the kind of ongoing performance monitoring this course covered earlier, Activity Monitor, Dynamic Management Views, Query Store, and Extended Events, DBCC is not the right primary tool. It has a narrow scope, it can affect performance meaningfully while it runs, and it was never designed for the real time visibility those other tools provide. A handful of commercial third party products, SQL Sentry and Redgate SQL Monitor among the better known ones, exist for broader cross server monitoring too, though this course has focused throughout on what SQL Server itself provides out of the box.
Where DBCC still earns a place in a monitoring routine is narrower and more deliberate than continuous observation: verifying one filegroup before taking a backup of it, checking or correcting an identity column after a bulk load, or diagnosing one specific suspected problem rather than watching an instance around the clock. This lesson covers the two commands that do exactly that, DBCC CHECKFILEGROUP and DBCC CHECKIDENT, plus a brief, deliberately short look at DBCC DBREINDEX, deprecated legacy syntax this module has already pointed past twice now.
DBCC CHECKFILEGROUP: Integrity Checks Scoped to One Filegroup
DBCC CHECKFILEGROUP checks the tables in a single filegroup rather than an entire database. It runs
CHECKALLOC on that filegroup and
CHECKTABLE on every table and indexed view inside it, the same kind of relationship
CHECKDB has to the narrower commands covered two lessons ago. If you have not worked through a filegroup before, think of it as a named group of one or more physical files that SQL Server treats as a single storage unit; every database has at least a primary filegroup, and a large database is often split across several so that backups, restores, and checks like this one can be scoped to a part of the database instead of the whole thing.
DBCC CHECKFILEGROUP
[
[ ( { filegroup_name | filegroup_id | 0 }
[ , NOINDEX ]
) ]
[ WITH
{
[ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , PHYSICAL_ONLY ]
[ , MAXDOP = number_of_processors ]
}
]
]
- filegroup_name | filegroup_id | 0: the filegroup to check. Omitted, or set to
0, checks the primary filegroup. FILESTREAM filegroups are not supported.
- NOINDEX: skips nonclustered indexes on the filegroup's tables, the same tradeoff
CHECKDB and CHECKTABLE offer.
- ALL_ERRORMSGS / NO_INFOMSGS / TABLOCK / ESTIMATEONLY / PHYSICAL_ONLY / MAXDOP: behave the same as their counterparts on the commands already covered in this module.
One real difference from
CHECKDB,
CHECKALLOC, and
CHECKTABLE is worth calling out before anyone assumes the repair options carry over by analogy:
CHECKFILEGROUP does not accept a repair option at all, not even a no-op one kept only for backward compatibility the way a couple of CHECKALLOC's options were. If it reports an error, the recommended path is restoring the affected filegroup from a verified backup. Without a usable backup,
DBCC CHECKDB run with the specific repair option named in the error output is the fallback, since CHECKDB is the command that actually supports repair.
Two practical reasons to reach for this command specifically rather than a full CHECKDB run: confirming a filegroup is error free before taking a filegroup level backup of it, and checking a very large database one filegroup at a time when a single full CHECKDB pass would take longer than an available maintenance window allows.
DBCC CHECKFILEGROUP ('PRIMARY') WITH NO_INFOMSGS;
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".
CHECKFILEGROUP found 0 allocation errors and 0 consistency errors in filegroup 'PRIMARY'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
One more specific detail worth knowing, especially if any of this course's examples ever move to a cloud hosted database: neither
CHECKDB nor
CHECKFILEGROUP is supported on Azure SQL Database Hyperscale.
DBCC CHECKTABLE ('table_name') WITH TABLOCK, run table by table across the tables you care about, is the documented workaround there, which is one more reason CHECKTABLE from the previous lesson is worth keeping in reach even once CHECKFILEGROUP is part of the routine.
Finding a filegroup's name or ID. A database that only ever uses the default primary filegroup never needs to look this up, but a database deliberately split across several does.
sys.filegroups lists every filegroup defined for the current database, along with which one is marked as the default target for a new object that does not specify a filegroup explicitly:
SELECT name, data_space_id, type_desc, is_default
FROM sys.filegroups
ORDER BY data_space_id;
data_space_id is the value
CHECKFILEGROUP accepts as
filegroup_id, and
name is the value it accepts as
filegroup_name. Either works equally well in the command itself, so the choice between them usually comes down to which one reads more clearly in a script meant for someone else to maintain later.
DBCC CHECKIDENT: Checking and Correcting an Identity Column
DBCC CHECKIDENT checks a table's current identity value and, depending on how it is called, can correct it. An identity column automatically generates a sequential value for every new row, and the "current identity value" DBCC reports is the next value SQL Server intends to hand out. The syntax here is simpler than anything else covered in this module, which makes the one real behavioral nuance easy to miss if you only skim it.
DBCC CHECKIDENT
(
table_name
[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]
- table_name: the table to check, which must have an identity column. A multi part name needs square bracket delimiters,
[Sales].[Orders], not double quotes, which only parse as an identifier when QUOTED_IDENTIFIER is on for the session, an easy mistake to carry over from code that happens to have that setting enabled elsewhere.
- NORESEED: reports the current identity value and the column's actual maximum value, side by side, without changing anything. Treat this as the diagnostic step, run before deciding what, if anything, needs fixing.
- RESEED [, new_reseed_value]: changes the current identity value, either to a value you specify explicitly, or, left without one, to the column's actual maximum value found by scanning the table.
- WITH NO_INFOMSGS: suppresses the informational message the command normally returns, the same option this module has used consistently to keep example output short.
The nuance worth being explicit about, because getting it wrong leaves a real problem unfixed while looking like it worked: calling
DBCC CHECKIDENT ('table_name') with no second argument at all is equivalent to calling it with
RESEED and no value. That form only corrects the identity if the current value is lower than the column's actual maximum; it does nothing at all if the current value is already correct, and critically, it does not fix an identity value that is too high. A too-high identity value is not a hypothetical edge case; it can happen after a manual reseed set the wrong number, or after certain restore and replication scenarios, and once it exists it causes every new insert to attempt a key value that already collides with an existing row, failing the insert rather than quietly corrupting anything. Fixing that specific case takes two deliberate steps rather than one bare call: run
NORESEED first to see the table's real maximum value, then run
RESEED again with that exact value supplied explicitly.
DBCC CHECKIDENT ('SalesDB.dbo.Orders');
Checking identity information: current identity value '48213', current column value '48213'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Matching values here mean the identity column is healthy and nothing needed correcting. If the current identity value had instead come back lower than the current column value, that gap is exactly what the bare call above would already have corrected on this same run, since a bare call behaves as
RESEED with no value supplied. Had it come back higher instead, this call would have reported the mismatch and done nothing about it, which is the scenario that calls for the explicit two-step
NORESEED then
RESEED sequence described above.
-- Step 1: find the real maximum without changing anything
DBCC CHECKIDENT ('SalesDB.dbo.Orders', NORESEED);
-- Step 2: reseed explicitly to that confirmed value
DBCC CHECKIDENT ('SalesDB.dbo.Orders', RESEED, 48213);
CHECKIDENT requires the same permission level as the other commands this module has covered: membership in the
sysadmin fixed server role, or
db_owner for the database being checked. It behaves the same way on Azure SQL Database and Azure SQL Managed Instance as it does against an on premises or VM hosted instance.
A common trigger worth connecting back to earlier material. This course's lessons on
BULK INSERT and
SELECT INTO both touched on loading a large batch of rows into an existing table in one operation. Either technique, run against a table that has an identity column, can leave that column's current value out of sync with its actual maximum, especially when the load explicitly supplies identity values of its own with
IDENTITY_INSERT turned on rather than letting SQL Server generate them. The usual symptom is a too-low current value, which the bare
DBCC CHECKIDENT ('table_name') call shown above corrects automatically on its own; a load that supplied identity values higher than the column's prior maximum, though, is exactly the too-high scenario that call cannot fix, and it is worth running
CHECKIDENT as a routine step right after any bulk load that touches an identity column, rather than waiting for the first collided insert to reveal the problem.
DBCC DBREINDEX: Deprecated Legacy Syntax
DBCC DBREINDEX rebuilds one or more indexes on a table, the equivalent of dropping and recreating an index in a single step, without having to manually drop and recreate any foreign key constraints along the way the way rebuilding a primary key's index by hand would otherwise require. That made it genuinely useful once, which is worth acknowledging rather than dismissing outright, but it has been deprecated since SQL Server 2005, the same deprecation date as
DBCC SHOWCONTIG from the previous lesson, in favor of
ALTER INDEX ... REBUILD, already demonstrated in this module's own corrected maintenance script. Its legacy syntax, worth recognizing if you ever read an old script rather than worth writing into a new one:
DBCC DBREINDEX
( [ 'database.owner.table_name' [, index_name [, fillfactor ] ] ]
) [WITH NO_INFOMSGS]
Nothing here needs new code to replace it. The
ALTER INDEX version built in the previous lesson's maintenance script already covers everything this command used to do, branching cleanly between
REORGANIZE and
REBUILD based on measured fragmentation, and it does so without the deprecation hanging over it.
Choosing Among This Module's DBCC Commands
With all six current commands from this module now covered, a quick side by side makes the right starting point for a given situation easier to pick out than scanning back through three separate lessons.
| Command | Scope | Reach for It When |
CHECKDB | Entire database | Running the routine, scheduled integrity check |
CHECKFILEGROUP | One filegroup | Verifying before a filegroup backup, or checking a very large database piece by piece |
CHECKTABLE | One table or indexed view | Suspected corruption in a specific table, or working around a Hyperscale limitation |
CHECKALLOC / CHECKCATALOG | Allocation only / system metadata only | A narrow, targeted question after a specific suspected cause |
CHECKIDENT | One identity column | After a bulk load, a manual reseed, or a restore that may have left an identity value wrong |
Every row above checks structural correctness, not performance, which is the thread running through this entire module. None of them tell you whether a query is slow; all of them tell you whether the data underneath that query is actually trustworthy.
One pattern worth noticing across all six: the commands that check a smaller scope,
CHECKFILEGROUP,
CHECKTABLE,
CHECKALLOC, and
CHECKCATALOG, are each already included inside a full
CHECKDB run. None of them needs to be scheduled as a routine replacement for CHECKDB; they earn their place as faster, narrower tools for a specific situation, a suspected problem in one table, a check before one filegroup's backup, a quick allocation only pass, rather than as a substitute for the comprehensive check covered in this module's first lesson.
CHECKIDENT stands apart from that pattern, since it checks something CHECKDB does not touch at all, an identity column's current value rather than the structural integrity of the data itself, which is exactly why it needs its own place in a maintenance routine instead of riding along inside a CHECKDB schedule.
DBCC's Place in This Course's Monitoring Toolkit
Stepping back across all three DBCC lessons in this module: a database can perform beautifully by every measure this course introduced earlier, fast queries, low wait times, a clean Activity Monitor dashboard, while still carrying a corrupted page, a fragmented index nobody has addressed, or a drifted identity column that nobody has noticed yet. None of those three problems necessarily shows up as a performance symptom until something trips over it directly, a query that fails outright against a damaged page, or an insert that collides with an identity value gone wrong.
That is the real argument for running DBCC checks on a schedule of their own, independent of whatever performance monitoring is already running day to day. A nightly PHYSICAL_ONLY check, a weekly full CHECKDB, and an identity check built into any process that does bulk loading, all run as SQL Server Agent jobs with failure notifications configured, catch structural problems on a predictable cadence rather than relying on a failed query or a collided insert to surface them after the fact. That is the same proactive posture this course has emphasized since its very first lesson on monitoring, just applied here to data correctness instead of performance.
It is also worth being honest about where DBCC's usefulness ends. None of the six commands covered across this module tell you why a query is slow, which wait type is dominating right now, or whether a recent deployment changed an execution plan for the worse; that is exactly the job this course assigned earlier to Activity Monitor, the Dynamic Management Views, Query Store, and Extended Events, and nothing here replaces any of them. A third party monitoring product can layer alerting, historical trending, and a dashboard on top of what SQL Server already exposes, but it is still built on the same underlying DMVs this course has already covered, not on DBCC. Treating DBCC as the integrity layer underneath everything else, checked on its own schedule, and the DMV based tools as the performance layer on top, is the division that keeps a monitoring routine from quietly missing one category of problem while watching closely for the other.
What's Ahead
The next lesson moves on to monitoring the transaction log, a shift away from DBCC entirely and back into territory this course's monitoring tools, log space Dynamic Management Views among them, were actually built for. DBCC's job in this course is now complete: six commands across three lessons, every one of them answering some version of the same underlying question, is the data actually what it should be, rather than how fast is it being served up.
