DBCC CHECKCATALOG (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Checks for catalog consistency within the specified database. The database must be online.
Transact-SQL syntax conventions
Syntax
DBCC CHECKCATALOG
[
(
database_name | database_id | 0
)
]
[ WITH NO_INFOMSGS ]
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
database_name | database_id | 0
The name or ID of the database for which to check catalog consistency. If not specified, or if 0 is specified, the current database is used. Database names must comply with the rules for identifiers.
WITH NO_INFOMSGS
Suppresses all informational messages.
Remarks
After the DBCC CHECKCATALOG
command finishes, a message is written to the SQL Server error log. If the DBCC command successfully executes, the message indicates a successful completion and the amount of time that the command ran. If the DBCC command stops before completing the check because of an error, the message indicates the command was terminated, a state value, and the amount of time the command ran. The following table lists and describes the state values that can be included in the message.
State | Description |
---|---|
0 | Error number 8930 was raised. This indicates a metadata corruption that caused the DBCC command to terminate. |
1 | Error number 8967 was raised. There was an internal DBCC error. |
2 | A failure occurred during emergency mode database repair. |
3 | This indicates a metadata corruption that caused the DBCC command to terminate. |
4 | An assert or access violation was detected. |
5 | An unknown error occurred that terminated the DBCC command. |
DBCC CHECKCATALOG
performs various consistency checks between system metadata tables. DBCC CHECKCATALOG
uses an internal database snapshot to provide the transactional consistency that it needs to perform these checks. For more information, see View the Size of the Sparse File of a Database Snapshot (Transact-SQL) and the DBCC internal database snapshot usage section in DBCC (Transact-SQL).
If a snapshot can't be created DBCC CHECKCATALOG
acquires an exclusive database lock to obtain the required consistency. If any inconsistencies are detected, they can't be repaired, and the database must be restored from a backup.
Note
Running DBCC CHECKCATALOG
against tempdb
does not perform any checks. This is because, for performance reasons, database snapshots are not available on tempdb
. This means that the required transactional consistency cannot be obtained. Restart the Database Engine service to resolve any tempdb
metadata issues.
Note
DBCC CHECKCATALOG
does not check FILESTREAM data. FILESTREAM stores binary large objects (BLOBS) on the file system.
DBCC CHECKCATALOG
is also run as part of DBCC CHECKDB.
Result sets
If no database is specified, DBCC CHECKCATALOG
returns:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If AdventureWorks2022
is specified as the database name, DBCC CHECKCATALOG
returns:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Permissions
Requires membership in the sysadmin fixed server role, or the db_owner fixed database role.
Examples
The following example checks the catalog integrity in both the current database and in the AdventureWorks2022
database.
-- Check the current database.
DBCC CHECKCATALOG;
GO
-- Check the AdventureWorks database.
DBCC CHECKCATALOG (AdventureWorks2022);
GO