DBCC CHECKDB SQL Server 数据库损坏故障排除

Summary: DBCC CHECKDB， DBCC， 数据库修复， 数据库， 损坏的数据库

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

问题：MS SQL Server 数据库已损坏，需要修复以保持稳定性和一致性。

Cause

MS SQL Server 数据库、日志和应用程序文件的软件损坏。

Resolution

DBCC 是 Microsoft SQL Server 的本机工具，旨在检查和修复 Microsoft SQL Server 数据库。  本文讨论 DBCC CHECKDB 命令以及检查数据库逻辑和物理完整性的选项。

DBCC CHECKDB 信息

DBCC CHECKDB （Transact-SQL）：

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15此超链接会将您带往 Dell Technologies 之外的网站。

运行 DBCC CHECKDB 的步骤：

  1. 打开 SQL Server Studio 管理
  2. 打开查询窗口
  3. 键入 “DBCC CHECKDB （Database Name）”

DBCC CHECKDB 运行的结果
 

        4.在查询结果底部附近检查以查看分配和一致性错误。 
        5.  如果结果中出现分配和一致性错误，则应从备份恢复数据库或使用 DBCC CHECKDB 执行数据库修复。          

DBCC 修复选项

  1. REPAIR_REBUILD：下面的命令将数据库置于允许修复的模式。 
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

复制以下命令并粘贴到输入正确数据库名称的查询会话：

--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 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

复制以下命令并粘贴到输入正确数据库名称的查询会话：

如果数据库物理磁盘空间不足，以下命令非常有用。

--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 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

复制下面的命令并粘贴到查询会话，并输入正确的数据库名称：

--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

有关何时使用 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.