合 MSSQL数据库一直显示“正在恢复”或“可疑”的解决办法

发布日期 2023年6月1日 · 已更新 2024年5月10日

0 635 2

Tags：故障处理 MSSQL SQL Server 可疑正在恢复

👉 本文共约1556个字，系统预计阅读时间或需6分钟。

文章目录[隐藏]

现象
- 正在恢复
- 数据库可疑
分析
解决
总结
参考

现象

正在恢复

由于磁盘空间满了，数据库处于“恢复挂起”状态，清理磁盘后，重启SQL Server服务器后，数据库一直显示“正在恢复”，用sp_who2查询显示该库处于“DB STARTUP”状态。

查询数据库阻塞，发现有个系统进程9在阻塞，然后使用kill 9报错：只能终止用户进程

消息 6107，级别 14，状态 1，第 3 行
只能终止用户进程。

1 2	消息 6107，级别 14，状态 1，第 3 行只能终止用户进程。

数据库可疑

若一直“正在恢复”，或显示“可疑”（Suspect），在 SQL Server 中，如果某个数据库被标记为“可疑”（Suspect），这通常意味着该数据库的事务日志文件（.ldf）可能损坏或丢失。当一个数据库的日志文件不可用时，SQL Server 无法保证数据库的一致性，因此会将数据库状态设置为“可疑”。

可以查询告警日志是否有如下输出：

An error occurred during recovery, preventing the database 'AB_STORE' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

During redoing of a logged operation in database 'AB_STORE', an error occurred at log record ID (1886659:12025:60). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

An error occurred during recovery, preventing the database 'AB_STORE' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

During redoing of a logged operation in database 'AB_STORE', an error occurred at log record ID (1886659:12025:60). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

若有，则说明恢复出现问题，可以使用本文的方法2进行恢复。

分析

SELECT name, state_desc FROM sys.databases;

1	SELECT name, state_desc FROM sys.databases;

恢复状态通常是“RECOVERING”。

首先查看进程的状态变化，物理IO和逻辑IO，如果没有变化，则先做1次SQL Server实例的关闭和启动，然后再查看进程的状态变化。


SELECT top 1000 
    (CASE WHEN ActualElapsedTime < 36000000
    THEN '0' ELSE '' END + RTRIM(ActualElapsedTime/1000/3600)
    + ':' + RIGHT('0' + RTRIM((ActualElapsedTime/1000)%3600/60), 2)
    + ':' + RIGHT('0' + RTRIM((ActualElapsedTime/1000)%60), 2))  ActualElapsedTime,
    a.wait_time2,
    a.spid,
    a.thread_cnt,
    -- a.dop,
    blocking_session_id blocking_spid,
    a.database_id,
    db_name(a.database_id) dbname,
    start_time,
    sp.last_batch,
    wait_type,
    last_wait_type,
    wait_resource,
    open_transaction_count,
    a.status,
    a.command,
    a.total_elapsed_time/1000 total_elapsed_time_s, 
    a.cpu_time/1000 cpu_time,
    a.writes,
    a.logical_reads,
    sp.physical_io,
    a.row_count,
    a.granted_query_memory*8/1024 granted_query_memory_MB,
    a.percent_complete,
    a.Time_Remaining,
    -- a.sql_handle,
    SUBSTRING (b.text,a.statement_start_offset/2,
       (CASE WHEN a.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), b.text)) * 2+ 2
            ELSE a.statement_end_offset END - a.statement_start_offset)/2+ 2) Internal_SQL_TEXT,
    b.text Parent_SQL_TEXT,
    -- a.plan_handle,
    -- c.query_plan,
    (select query_plan from sys.dm_exec_query_plan(a.plan_handle) n1) query_plan,
    @@spid current_spid,
    'kill '+cast(a.spid as varchar) kill1
FROM  (
    select nb.session_id spid,
            nb.total_elapsed_time/1000 total_elapsed_time_s,
            (CASE WHEN estimated_completion_time < 36000000
            THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
            + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
            + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2)) AS [Time_Remaining],
            (SELECT count(*) FROM master.dbo.sysprocesses sp where sp.spid=nb.session_id) thread_cnt,
           DATEDIFF(MILLISECOND, nb.start_time, getdate()) ActualElapsedTime,
            (CASE WHEN wait_time < 36000000
            THEN '0' ELSE '' END + RTRIM(wait_time/1000/3600)
            + ':' + RIGHT('0' + RTRIM((wait_time/1000)%3600/60), 2)
            + ':' + RIGHT('0' + RTRIM((wait_time/1000)%60), 2)) AS wait_time2,
           nb.* 
    from sys.dm_exec_requests nb WITH(NOLOCK) 
  ) a
LEFT JOIN master.dbo.sysprocesses sp WITH(NOLOCK) 
on  sp.spid=a.spid and sp.ecid=0
LEFT JOIN
( SELECT n1.session_id ,n2.text
 FROM sys.dm_exec_requests n1 WITH(NOLOCK)  CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) b
on a.spid=b.session_id
WHERE a.spid <> @@spid 
and
(a.status not in ('sleeping','background') 
    or (a.status in ('sleeping','background')  and a.spid IN (select blocking_session_id from sys.dm_exec_requests nb)) 
    or (a.status in ('sleeping','background') and a.open_transaction_count > 0 )
    or ( a.status in ('background') and a.command='DB STARTUP' )
    )
--  and a.spid in (141,135)
order by cast(ActualElapsedTime as numeric) desc 
GO

SELECT top 1000

(CASE WHEN ActualElapsedTime < 36000000

THEN '0' ELSE '' END + RTRIM(ActualElapsedTime/1000/3600)

+ ':' + RIGHT('0' + RTRIM((ActualElapsedTime/1000)%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((ActualElapsedTime/1000)%60), 2)) ActualElapsedTime,

a.wait_time2,

a.spid,

a.thread_cnt,

-- a.dop,

blocking_session_id blocking_spid,

a.database_id,

db_name(a.database_id) dbname,

start_time,

sp.last_batch,

wait_type,

last_wait_type,

wait_resource,

open_transaction_count,

a.status,

a.command,

a.total_elapsed_time/1000 total_elapsed_time_s,

a.cpu_time/1000 cpu_time,

a.writes,

a.logical_reads,

sp.physical_io,

a.row_count,

a.granted_query_memory*8/1024 granted_query_memory_MB,

a.percent_complete,

a.Time_Remaining,

-- a.sql_handle,

SUBSTRING (b.text,a.statement_start_offset/2,

(CASE WHEN a.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), b.text)) * 2+ 2

ELSE a.statement_end_offset END - a.statement_start_offset)/2+ 2) Internal_SQL_TEXT,

b.text Parent_SQL_TEXT,

-- a.plan_handle,

-- c.query_plan,

(select query_plan from sys.dm_exec_query_plan(a.plan_handle) n1) query_plan,

@@spid current_spid,

'kill '+cast(a.spid as varchar) kill1

FROM (

select nb.session_id spid,

nb.total_elapsed_time/1000 total_elapsed_time_s,

(CASE WHEN estimated_completion_time < 36000000

THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2)) AS [Time_Remaining],

(SELECT count(*) FROM master.dbo.sysprocesses sp where sp.spid=nb.session_id) thread_cnt,

DATEDIFF(MILLISECOND, nb.start_time, getdate()) ActualElapsedTime,

(CASE WHEN wait_time < 36000000

THEN '0' ELSE '' END + RTRIM(wait_time/1000/3600)

+ ':' + RIGHT('0' + RTRIM((wait_time/1000)%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((wait_time/1000)%60), 2)) AS wait_time2,

nb.*

from sys.dm_exec_requests nb WITH(NOLOCK)

) a

LEFT JOIN master.dbo.sysprocesses sp WITH(NOLOCK)

on sp.spid=a.spid and sp.ecid=0

LEFT JOIN

( SELECT n1.session_id ,n2.text

FROM sys.dm_exec_requests n1 WITH(NOLOCK) CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) b

on a.spid=b.session_id

WHERE a.spid <> @@spid

and

(a.status not in ('sleeping','background')

or (a.status in ('sleeping','background') and a.spid IN (select blocking_session_id from sys.dm_exec_requests nb))

or (a.status in ('sleeping','background') and a.open_transaction_count > 0 )

or ( a.status in ('background') and a.command='DB STARTUP' )

)

-- and a.spid in (141,135)

order by cast(ActualElapsedTime as numeric) desc

如果物理IO和逻辑IO有变化，则说明在恢复中，只需要等待即可，不需要做任何操作。

恢复进入到后期时，percent_complete列也会指示恢复的进度。

若没有变化，则可能需要按照如下的几种方式来恢复。

请不要轻易使用如下方法。

解决

方法1

-- RESTORE database <数据库名> with norecovery;

-- 执行以下命令来强制结束数据库的恢复：
ALTER DATABASE [数据库名] SET OFFLINE;

-- RESTORE database <数据库名> with norecovery;

-- 执行以下命令来强制结束数据库的恢复：

ALTER DATABASE [数据库名] SET OFFLINE;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

可能会报错：

用户没有更改数据库 'TEST' 的权限，该数据库不存在或数据库未处于允许访问检查的状态----解决：需要等待恢复完成或恢复报错
由于无法在数据库 'PT_STORE' 上放置锁，ALTER DATABASE 失败”

可以尝试方法2和方法3。

方法2


USE master
GO
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE test SET EMERGENCY
GO
DBCC CHECKDB(test,REPAIR_ALLOW_DATA_LOSS)
go
-- DBCC CHECKDB(test, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
-- GO

-- 重建日志文件: 完成上述步骤后，你可能需要重新创建日志文件。可以通过以下命令来执行此操作：
-- USE master;
-- ALTER DATABASE test REBUILD;

ALTER DATABASE test SET ONLINE
GO
ALTER DATABASE test SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

USE master

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE test SET EMERGENCY

DBCC CHECKDB(test,REPAIR_ALLOW_DATA_LOSS)

-- DBCC CHECKDB(test, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

-- GO

-- 重建日志文件: 完成上述步骤后，你可能需要重新创建日志文件。可以通过以下命令来执行此操作：

-- USE master;

-- ALTER DATABASE test REBUILD;

ALTER DATABASE test SET ONLINE

ALTER DATABASE test SET MULTI_USER WITH ROLLBACK IMMEDIATE;

请注意，上述命令将数据库设置为紧急模式，然后修复数据库。但这可能导致数据丢失。在执行此操作之前，请务必进行备份，并在理解潜在风险的情况下执行。

可能会报错：

用户没有更改数据库 'TEST' 的权限，该数据库不存在或数据库未处于允许访问检查的状态
由于无法在数据库 'PT_STORE' 上放置锁，ALTER DATABASE 失败”

方法3：已实践，比较靠谱

1.停用SQL Server服务

2.将数据库的.mdf和.ldf文件修改为其它名称，例如添加bk结尾

3.启用SQL Server服务

4.右键删除数据库

5.将第二步更名后的.mdf和.ldf文件名称改回原来的数据库文件名。

6.在管理工具中重新附加数据库（日志较多时，可能会比较慢）。

总结

1、还原或恢复数据库的进度可以通过sys.dm_exec_requests er的percent_complete字段来查询，也可以在还原的时候添加STATS=10这个选项来显示还原的进度。参考：https://www.dbaup.com/shujukudangqianhuihuachaxunhuizongdangqianxingnengwentikuaisufenxi.html

2、使用sa用户或Windows身份验证登陆

3、新建1433的防火墙规则，禁止其它客户端登陆

参考

https://www.cnblogs.com/KJXY/articles/14850606.html

https://zhuanlan.zhihu.com/p/691677356

打赏赞(2)

标签：故障处理 MSSQL SQL Server 可疑正在恢复

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。
5、愿大家出走半生，归来仍是少年！！！

合 MSSQL数据库一直显示“正在恢复”或“可疑”的解决办法

现象

正在恢复

数据库可疑

分析

解决

方法1

方法2

方法3：已实践，比较靠谱

总结

参考

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2026 年 3 月
一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

合 MSSQL数据库一直显示“正在恢复”或“可疑”的解决办法

现象

正在恢复

数据库可疑

分析

解决

方法1

方法2

方法3：已实践，比较靠谱

总结

参考

相关文章

您可能还喜欢...

告警日志报“ORA-01565: Unable To open Spfile”

OGG故障OGG-12029、OGG-08241、OGG-08221、OGG-01758、OGG-02870、RMAN-08137

发表回复 取消回复

网站公告

网站寄语

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复