DBCC CHECKDB Troubleshooting SQL Server Database Corruption

Summary: DBCC is a native tool of Microsoft SQL Server designed to check and repair Microsoft SQL Server Databases.  This article discusses the DBCC CHECKDB command and options in checking database logical and physical integrity. ...

This article applies to This article does not apply to This article is not tied to any specific product. Not all product versions are identified in this article.
Check out other resources

Symptoms

Microsoft SQL Server database is corrupt and needs to be repaired to maintain stability and consistency. 

Cause

Software corruption to MS SQL Server database, logs, and application files.

Resolution

DBCC CHECKDB information

DBCC CHECKDB (Transact-SQL):

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15This hyperlink is taking you to a website outside of Dell Technologies.

Steps to run DBCC CHECKDB:

  1. Open SQL Server Studio Management
  2. Open a query window
  3. Type in "DBCC CHECKDB (Database Name)"

Results for DBCC CHECKDB run
 

        4. Check near the bottom of query results to see allocation and consistency errors. 
        5.  If allocation and consistency errors appear in the results the database should either be restored from backup or perform a database repair using DBCC CHECKDB.          

DBCC Repair Options

  1. REPAIR_REBUILD: Command below puts the database in a mode allowing repair. 
USE Master;
GO
ALTER DATABASE TSQL2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (TSQL2012, REPAIR_REBUILD)
GO
ALTER DATABASE TSQL2012
SET MULTI_USER;
GO

Copy the command below and paste to the query session putting in the correct database name:

--Command below performs a Database repair not allowing Data loss

USE Master;
GO
ALTER DATABASE TSQL2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (TSQL2012,REPAIR_REBUILD)
GO
ALTER DATABASE TSQL2012
SET MULTI_USER;
GO

       2. REPAIR_ALLOW_DATA_LOSS: Command below performs database repair and may result in data loss to complete the repair process.

--Command below performs a database repair that may result in data loss,
 --replace TSQL2012 with the database name of database being repaired
USE Master
GO
ALTER DATABASE TSQL2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (TSQL2012, REPAIR_ALLOW_DATA_LOSS)
GO
ALTERDATABASE TSQL2012
SET MULTI_USER
GO

Copy the command below and paste to the query session putting in the correct database name:

Commands below are useful if database physical disk space is running low.

--Command below estimates the size TEMPDB must be to complete the DBCC CHECKDB job
USE Master;
GO
ALTER DATABASE TSQL2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (TSQL2012, REPAIR_REBUILD)
WITH ESTIMATEONLY;
GO
ALTER DATABASE TSQL2012
SET MULTI_USER
GO

Copy the command below and paste to the query session putting in the correct database name:

--Command below estimates the size TEMPDB must be to complete the DBCC CHECKDB job

USE Master;
GO
ALTER DATABASE TSQL2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (TSQL2012, REPAIR_REBUILD)
WITH ESTIMATEONLY;
GO
ALTER DATABASE TSQL2012
SET MULTI_USER
GO

 

--Command below will run DBCC CHECKDB without using TEMDB and creating a DB 
--snapshot. When not using TEMPDB with a DB snapshot all data will be checked in
-- the live database not not a database snapshot.

USE Master;
GO
ALTER DATABASE TSQL2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (TSQL2012, REPAIR_REBUILD)
WITH TABLOCK;
GO
ALTER DATABASE TSQL2012
SET MULTI_USER
GO

Copy the command below and paste to query session and put in the correct database name:

--Command below will run DBCC CHECKDB without using TEMPDB and creating a DB 
--snapshot. When not using TEMPDB with a DB snapshot all data will be checked in ---the live database not a database snapshot.

USE Master;
GO
ALTER DATABASE TSQL2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (TSQL2012, REPAIR_REBUILD)
WITH TABLOCK;
GO
ALTER DATABASE TSQL2012
SET MULTI_USER
GO

Information on when to use DBCC CHECKDB

Affected Products

Dell Quickstart Data Warehouse Appliance, Prosupport for Microsoft Software
Article Properties
Article Number: 000134529
Article Type: Solution
Last Modified: 18 Mar 2025
Version:  5
Find answers to your questions from other Dell users
Support Services
Check if your device is covered by Support Services.