SQL 如果数据库总是进入 RECOVERY，这意味着什么？

What does it mean if the database always keeps going into RECOVERY?

提问by Legend

Every time I run a query, my database does not respond to an immediate second query and complains that it is in recovery mode (though it does not show anything beside the database name). This happens for about 5-10 minutes after which everything goes back to being normal.

每次运行查询时，我的数据库都不会立即响应第二个查询，并抱怨它处于恢复模式（尽管除了数据库名称之外没有显示任何内容）。这会发生大约 5-10 分钟，之后一切都会恢复正常。

I am expecting a major crash so I am copying the tables into a different database but anyone knows why this could happen or if there is a permanent fix?

我预计会发生重大崩溃，因此我将表复制到不同的数据库中，但有人知道为什么会发生这种情况，或者是否有永久修复？

回答by jveazey

Normally, a database is only in "Recovery" mode during startup - when SQL Server starts up the database. If your database goes into Recovery mode because of a SQL statement, you almost definitely have some sort of corruption.

通常，数据库仅在启动期间处于“恢复”模式 - 当 SQL Server 启动数据库时。如果您的数据库由于 SQL 语句而进入恢复模式，那么您几乎肯定会出现某种损坏。

This corruption can take one of many forms and can be difficult to diagnose. Before you do anything, you need to check a few things.

这种损坏可以采用多种形式中的一种，并且很难诊断。在你做任何事情之前，你需要检查一些事情。

1. Make sure you have good backups of your database - copied onto a separate file system/server.
2. Check Windows Event Logand look for errors. If any critical errors are found, contact Microsoft.
3. Check SQL Server ERRORLOGand look for errors. If any critical errors are found, contact Microsoft.
4. Run chkdskon all the hard drives on the server.
5. Run dbcc checkdbagainst your database. If any errors are found, you can attempt to fix the database with the REPAIR_REBUILD option. If any errors could not be fixed, contact Microsoft.
6. Restore a backup copy of your database onto a different server. This will confirm whether it is a problem withinyour database or the SQL Server/machine.

1. 确保您有数据库的良好备份 - 复制到单独的文件系统/服务器上。
2. 检查Windows 事件日志并查找错误。如果发现任何严重错误，请联系 Microsoft。
3. 检查SQL Server ERRORLOG并查找错误。如果发现任何严重错误，请联系 Microsoft。
4. 在服务器上的所有硬盘驱动器上运行chkdsk。
5. 对您的数据库运行dbcc checkdb。如果发现任何错误，您可以尝试使用 REPAIR_REBUILD 选项修复数据库。如果无法修复任何错误，请联系 Microsoft。
6. 将数据库的备份副本还原到不同的服务器上。这将确认它是否是一个问题中的数据库或SQL Server /机。

After step #4, #5, and #6, run your queries again to see if you can cause the database to go into Recovery mode. Unfortunately, corruption can occur because of an untold number of reasons, but more important than anything is the data. It will confirm whether it is a problem with your data or elsewhere. As long as you have backups that can be restored to a different SQL Server and a restored copy does not continually go into Recovery mode, you don't have to worry too much.

在步骤 #4、#5 和 #6 之后，再次运行查询以查看是否可以使数据库进入恢复模式。不幸的是，损坏可能由于无数原因而发生，但比任何事情都重要的是数据。它将确认这是您的数据还是其他地方的问题。只要您的备份可以还原到不同的 SQL Server，并且还原的副本不会持续进入恢复模式，您就不必太担心。

I always put Number 6 last because setting up a separate server with SQL Server and moving/restoring a large database can take an extensive amount of time; but if you already have a backup/test server in place, this might be a good first option. After all, it won't cause any downtime with your live server.

我总是把数字 6 放在最后，因为使用 SQL Server 设置单独的服务器和移动/恢复大型数据库可能需要大量时间；但是如果您已经有一个备份/测试服务器，这可能是一个不错的第一选择。毕竟，它不会导致您的实时服务器停机。

Finally, don't be afraid to contact Microsoft over this. Databases are often mission-critical, and Microsoft has plenty of tools at their disposal to diagnose problems just like this.

最后，不要害怕就此与 Microsoft 联系。数据库通常是关键任务，Microsoft 有很多工具可以用来诊断此类问题。

回答by gbn

Late answer...

迟到的回答...

Does your database have autocloseset to true? When set, the DBMS has to bring the database online which may account for your symptoms

您的数据库是否将自动关闭设置为 true？设置后，DBMS 必须使数据库联机，这可能会解释您的症状

回答by stanley

This can happen when the SQL Server Service has gone down hard in the middle of write operations and sometimes during mode during server startup. Follow the query in this link to monitor

这可能发生在 SQL Server 服务在写入操作过程中出现严重故障时，有时在服务器启动期间的模式下。按照此链接中的查询进行监控

http://errorbank.blogspot.com/2012/09/mssql-server-database-in-recovery.html

http://errorbank.blogspot.com/2012/09/mssql-server-database-in-recovery.html

回答by RQDQ

I've only had this happen when the service (or the SQL Server Service) has gone down hard in the middle of write operations. Once it came back, everything was fine.

我只在服务（或 SQL Server 服务）在写操作过程中出现严重故障时才会发生这种情况。回来后，一切都好了。

However, if this happening often, then I would suspect a disk level failure of some sort. I would make sure the database is fully backed up and move it to another server while you run diagnostics / rebuild the problem server.

但是，如果这种情况经常发生，那么我会怀疑某种磁盘级故障。当您运行诊断程序/重建问题服务器时，我会确保数据库已完全备份并将其移动到另一台服务器。