一则SQLServer报错处理

CHECKDB结果如下：

Msg 8905, Level 16, State 1, Line 1
Extent (1:3887112) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 1
Extent (1:3887152) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
Msg 8910, Level 16, State 1, Line 1
Page (1:2822888) in database ID 7 is allocated to both object ID 674101442, index ID 11, partition ID 72057594067288064, alloc unit ID 72057594073186304 (type In-row data), and object ID 802817922, index ID 1, partition ID 72057594305642496, alloc unit ID 72057594314358784 (type In-row data).
Msg 8906, Level 16, State 1, Line 1
Page (1:3684833) in database ID 7 is allocated in the SGAM (1:3578625) and PFS (1:3680040), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
CHECKDB found 2 allocation errors and 0 consistency errors in table 'REPOSITORY.CHANGE_EVENT' (object ID 674101442).
CHECKDB found 4 allocation errors and 0 consistency errors in database 'ccm'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ccm).
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

其中GAM和SGAM解释如下：

• 全局分配映射表 (GAM) ：统一盘区，GAM 页记录已分配的区。每个 GAM 包含 64,000 个区，相当于近 4 GB 的数据。GAM 用一个位来表示所涵盖区间内的每个区的状态。如果位为 1，则区可用；如果位为 0，则区已分配。
• 共享全局分配映射表 (SGAM) ：由多个对象共同拥有该盘区，SGAM 页记录当前用作混合区且至少有一个未使用的页的区。每个 SGAM 包含 64,000 个区，相当于近 4 GB 的数据。SGAM 用一个位来表示所涵盖区间内的每个区的状态。如果位为 1，则区正用作混合区且有可用页。如果位为 0，则区未用作混合区，或者虽然用作混合区但其所有页均在使用中。
• 索引分配映射 (IAM)页：将映射分配单元使用的数据库文件中 4 GB 部分中的区。主要用来标示 Sql Server 对象使用了哪些区。比如A表的非聚集索引B占用了 7895 4个区。这样，在再对此对象做更改的时候，能够快速的找到这4个区，更改文件。

由于提示说明：repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ccm).
因此尝试使用dbcc进行修复，这种方式会有数据丢失但已经是最小修复级别。

将数据库状态改为紧急模式

ALTER DATABASE test SETEMERGENCY

GO

将数据库改为单用户访问

ALTER DATABASE test SET SINGLE_USER

GO

运行repair_allow_data_loss修复

DBCC CHECKDB(test,repair_allow_data_loss)

Go

修复完成后运行DBCC CHECKDB确定没有问题

DBCC CHECKDB with NO_INFOMSGS

Go

将数据库更改为多用户访问

ALTER DATABASE test SET MULTI_USER