How to repair suspect SQL Server

suspect.md

如何修正Suspect状态的SQL Server

出现数据库Suspect状态后，不要慌。让我来拯救你吧。

Suspect状态是什么

SQL Server本身会以多种方式存储最终份的数据库。包括事务方法、快照方法等。SQL Server会在启动时检查多种方式存储的最终数据的结果，一旦发现任何数据不一致的形态，都会导致数据库无法启动。

所以，遇到这种情况，不要慌。基本上数据都能恢复过来，只是最后恢复起来以后可能不是和当初一致的内容。

导致Suspect状态的原因

根据上面讨论的内容，只要数据写入遇到了障碍，都会导致下次启动时，数据库为Suspect状态。

常见导致Suspect状态的原因有：

• 系统无法打开数据或日志文件所在的设备
• 在创建或打开物理设备期间找不到指定的文件
• SQL Server在事务中间崩溃
• 由于安装了防病毒软件，因此在上线时无法访问数据或日志文件
• 数据库服务器非法关机
• 磁盘空间不足导致无法写入
• SQL无法完成回滚或前滚操作
• 数据库文件被第三方备份软件锁定

如何解决

先找到问题的原因。请回溯上一个步骤，检查是否是上述原因导致的。例如，磁盘空间不足。

针对根本原因，要先解决根本问题，再修数据库。

包括：

• 检查系统是否有更新，是否有严重安全隐患，是否有黑客侵入
• 检查系统电源是否稳固
• 检查磁盘空间是否充足
• 检查数据库文件是否对数据库进程有读写权限
• 检查是否安装了第三方杀毒软件
• 检查是否安装了第三方备份软件
• 尝试将数据库文件挂载到其它计算机上

如果已经解决了根本问题，可以遵循下列步骤。注意，在开始前，如果数据库是虚拟机，强烈建议进行虚拟机快照。

10步修复法

1. 打开SSMS

打开SSMS，连入故障SQL Server实例。

2. 准备执行SQL

找到Suspect状态的数据库，注意千万不要展开它。直接新建查询即可。

3. 将数据库设置为紧急状态

执行下列SQL

EXEC sp_resetstatus BBLEARN;
ALTER DATABASE BBLEARN SET EMERGENCY

这会将数据库设置为紧急状态，以允许进一步修复。

4. 对数据库运行错误扫描

执行下列SQL

DBCC CHECKDB (database_name)
DBCC CHECKCATALOG (database_name)

这会对当前故障数据库运行故障扫描。你可能会看到大量故障。可以依次检查这些错误。注意，这一步将不会修正任何错误。

5. 准备修复

要想运行数据库修复脚本，必须将数据库设置为单用户。这也可以阻止在修复过程中可能的其它人不小心连进来。

同时，我们要回滚最近的一次事务，因为最后一次事务一定是失败的，并且很可能正是因为最后一次事务才导致了数据库故障。

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

6. 运行修复

DBCC CHECKDB (database_name, REPAIR_FAST) --快速修正
DBCC CHECKDB (database_name, REPAIR_REBUILD) --重置索引
DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS) --允许丢失数据的修正
DBCC CHECKALLOC (database_name, REPAIR_REBUILD) --修正分配问题
DBCC DBREINDEX (database_name, REPAIR_REBUILD) --修正索引问题

上面代码将尝试修复数据库错误。如果你在执行时遇到了障碍，请检查文末的高频问题章节。

注意，上面代码中，REPAIR_ALLOW_DATA_LOSS 表示允许为了修复数据库而舍弃数据。第六步可能需要很久，一般对于30GB左右的数据库需要数个小时。需要耐心等待。

运行一次上述SQL可能无法完全修正，使用下面SQL可以多次循环修正错误。

DECLARE @Number INT = 1 ;
while @Number < =   10
begin
PRINT @Number;
SET @Number = @Number + 1 ;
DBCC CHECKDB(BBLEARN, REPAIR_ALLOW_DATA_LOSS)
end

7. 重新允许数据库的多用户连接

此时，数据库已经恢复完成。我们可以重新允许数据库的多人连接。

ALTER DATABASE database_name SET MULTI_USER

8. 立即备份

此时，数据库已经抵达了一个可用的状态。应当立即对数据库进行备份，以导出一份可用的数据状态。如果数据库是虚拟机，建议备份的同时立即快照。

同时，建议重新启动整个数据库服务器，检查重启后数据库是否仍然正常。这是为了避免可能的原因重复导致数据库suspect。

9. 检查数据

在取得了可用的数据后，我们可以检查当前数据库状态是否是一个较新的状态。

10. 修正其它错误

在上面过程结束后，虽然大部分数据已经可用，但是数据库中仍然有较多错误。

可以使用下列SQL查看到这些错误：

DBCC CHECKDB (database_name)

如果要修正这部分错误，可以尝试先备份，再恢复备份，这可能能解决这部分问题。

高频问题

当数据库处于单用户状态时，我们无法离开当前连接。因为一旦我们离开，可能会有其它连接直接占用进来。

在这种情况下，我们必须手动杀死冲进来的其它连接，保证我们是唯一一个正在操作数据库的用户。

方法如下：

先执行下列SQL

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'testdb01'
go

会看到输出的session列表，找出这个冲进来的连接的SPID。例如，SPID是51.

再执行下列SQL

kill 51
go

此时，我们就可以继续对这个单用户数据库执行SQL了。