了解不同SQL Server数据库状态

Identifying the SQL Server database state and how a database can be moved between these different states is considered an important aspect of SQL Server database administration . A good understanding of this will help us in troubleshooting and fixing many database problems and issues.

识别SQL Server数据库状态以及如何在这些不同状态之间移动数据库被认为是SQL Server数据库管理的重要方面。 对此有一个很好的了解将有助于我们进行故障排除和修复许多数据库问题。

A SQL Server database state specifies the current running mode of that database. The database can be running in one state at a given time. The current state of a database can be verified by selecting the state_desc column of the sys.databases catalog view.

SQL Server数据库状态指定该数据库的当前运行模式。 数据库可以在给定时间以一种状态运行。 可以通过选择sys.databases目录视图的state_desc列来验证数据库的当前状态。

There are seven main states in which a SQL Server database can exit. The below SELECT statement will query the sys.databases catalog view for the name and state for all databases hosted in the current SQL Server instance:

SQL Server数据库可以退出的七个主要状态。 下面的SELECT语句将查询sys.databases目录视图，以获取当前SQL Server实例中托管的所有数据库的名称和状态：

 
SELECT name, state_desc  FROM sys.databases
 

The result, in my situation, will show that all databases hosted in the current SQL Server instance are operating in the ONLINE state as follows:

根据我的情况，结果将显示当前SQL Server实例中托管的所有数据库都处于ONLINE状态，如下所示：

The current state of a specific SQL Server database can be viewed also by selecting the Status property from the DATABASEPROPERTYEX function. The below SELECT statement views the current status of the SQLShackDemo database using the DATABASEPROPERTYEX function:

也可以通过从DATABASEPROPERTYEX函数中选择Status属性来查看特定SQL Server数据库的当前状态。 下面的SELECT语句使用DATABASEPROPERTYEX函数查看SQLShackDemo数据库的当前状态：

 
SELECT DATABASEPROPERTYEX (N'SQLShackDemo', N'STATUS') AS N'Status';
GO
 

Which is online as shown in the result below:

在线显示如下结果：

From the database availability side, the database can be fully available or fully unavailable. Between these two main states, a smooth transition should have occurred in optimal scenarios free of any problems that may interrupt that transition. In this article, we will describe the seven database states, the reasons for these SQL database states occurrences and how the database will act when operating in those states.

从数据库可用性方面，数据库可以完全可用或完全不可用。 在这两个主要状态之间，在最佳情况下应该已经发生了平稳的过渡，而没有任何可能中断过渡的问题。 在本文中，我们将描述七个数据库状态，这些SQL数据库状态出现的原因以及在这些状态下操作时数据库将如何运行。

线上 (ONLINE)

A SQL database that operates in an ONLINE state is available for end users access and functioning normally. In the ONLINE database state, the primary filegroup is online, although the critical database recovery process of the undo phase may still not have finished completely. The ONLINE state is the healthy state that the SQL database should move to smoothly after starting up the database.

在联机状态下运行SQL数据库可供最终用户访问并正常运行。 在联机数据库状态下，主文件组处于联机状态，尽管撤消阶段的关键数据库恢复过程可能仍未完全完成。 ONLINE状态是启动数据库后SQL数据库应平稳移动的健康状态。

From the Databases node of the SQL Server Management Studio, the database name e.g. SQLShackDemo with no special words between brackets, as shown below, indicates that the database is in ONLINE state

在SQL Server Management Studio的“数据库”节点上，数据库名称（例如SQLShackDemo）在方括号之间没有特殊词，如下所示，指示数据库处于联机状态

还原 (RESTORING)

The RESTORING database state means that the user has initiated a database restore process, using RESTORE DATABASE or RESTORE LOG T-SQL command, in which one or more data files of the Primary filegroup is restored, or one or more secondary files are being restored in offline mode. In effect thish means that the database is not available for the end user access during the restoration process.

RESTORING数据库状态表示用户已使用RESTORE DATABASE或RESTORE LOG T-SQL命令启动了数据库还原过程，其中还原了主要文件组的一个或多个数据文件，或者正在还原一个或多个辅助文件。离线模式。 实际上，这意味着在还原过程中该数据库不可用于最终用户访问。

The default database restore option is the RECOVERY option, in which the database will be brought online back after completing the database backup restoration. Using the NORECOVERY restore option, that is used to restore multiple backup files, the database will be in the RESTORING state until it reaches to the last file in which the WITH RECOVERY option is used to bring the database online again after restoring the last backup file.

默认的数据库还原选项是RECOVERY选项，在该数据库中，完成数据库备份还原后将使数据库重新联机。 使用NORECOVERY restore选项（用于还原多个备份文件），数据库将一直处于RESTORING状态，直到到达最后一个文件为止，在该文件中，使用WITH RECOVERY选项用于在还原最后一个备份文件后使数据库再次联机。

The below RESTORE DATABASE command using the NORECOVERY restore option will keep the database in RESTORING state:

下面的使用NORECOVERY restore选项的RESTORE DATABASE命令将使数据库保持RESTORING状态：

 
USE [master]
RESTORE DATABASE [SQLShackDemo] 
FROM  DISK = N'D:\backupSQL\SQLShackDemo_2016-08-19-001218.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5 , NORECOVERY
GO
 

The same process can be performed using the Restore Database window of the SQL Server Management Studio. The database restore option can be specified from the Options tab of that window as follows:

可以使用SQL Server Management Studio的“还原数据库”窗口执行相同的过程。 可以从该窗口的“选项”选项卡中指定数据库还原选项，如下所示：

Performing the previous restoration process, the database will be in the RESTORING state, with the special word “Restoring” between brackets beside the database name, as shown below, indicating that the database is in RESTORING state:

执行先前的还原过程，数据库将处于RESTORING状态，在数据库名称旁边的方括号之间带有特殊词“ Restoring”，如下所示，表明数据库处于RESTORING状态：

If the database still operating in the RESTORING state and there is no backup file to be restored to the database, you can recover the database and bring it ONLINE simply by applying the below RESTORE DATABASE …. WITH RECOVERY command:

如果数据库仍处于RESTORING状态，并且没有要还原到数据库的备份文件，则可以通过应用以下RESTORE DATABASE来恢复数据库并使其联机。 WITH RECOVERY命令：

 
USE [master]
RESTORE DATABASE [SQLShackDemo] 
WITH RECOVERY
GO
 

The database will be brought online again by restoring no new page as follows:

通过不按以下方式恢复新页面将使数据库再次联机：

恢复 (RECOVERING)

The Database RECOVERING state is a transient state, in which the database is performing a recovery process and will become ONLINE automatically, if the recovery process completed successfully, after the database startup.

Database RECOVERING状态是过渡状态，在该状态下数据库正在执行恢复过程，并且在数据库启动后，如果恢复过程成功完成，数据库将自动变为ONLINE。

The recovery process consists of two main phases, the Roll Forward phase, in which any transaction that is committed while shutting the database down and not yet written to the database data files will be processed. In the Rollback phase, any transaction that is uncommitted during the database shutting down will be rolled back. If the recovery process failed for any reason, the database will be moved to the SUSPECT state and will become unavailable. Working in RECOVERING state, the database will be unavailable for the users.

恢复过程包括两个主要阶段，即前滚阶段，在该阶段中，将处理在关闭数据库时已提交但尚未写入数据库数据文件的任何事务。 在回滚阶段，数据库关闭期间未提交的任何事务都将被回滚。 如果恢复过程由于任何原因而失败，则数据库将被移至SUSPECT状态，并且将变得不可用。 在RECOVERING状态下，用户将无法使用该数据库。

The special word “In Recovery”, between brackets beside the database name indicates, as shown below, that the database is in RECOVERING state:

如下所示，在数据库名称旁边的方括号之间的特殊字词“ In Recovery”表示数据库处于RECOVERING状态：

The RECOVERING database state is a transient state as we mentioned previously, that is performed on the database startup or after restoring the last backup file. In normal cases, the database will not be in the RECOVERING state for long time. One of the most common issues that leads the database to stay in RECOVERING state, for a longer time and slow down the recovery process, is an excessive number of Virtual Log Files (VLFs), up to tens thousands of them, inside the database transaction log. 

如前所述，RECOVERING数据库状态是一个过渡状态，在数据库启动时或还原最后一个备份文件后执行。 在正常情况下，数据库长时间不会处于RECOVERING状态。 导致数据库长时间停留在RECOVERING状态并延缓恢复过程的最常见问题之一是数据库事务中有过多的虚拟日志文件 （VLF），多达数万个日志。

The number of VLFs can be verified by running the below DBCC command on your database:

可以通过在数据库上运行以下DBCC命令来验证VLF的数量：

 
USE SQLShackDemo
GO
DBCC LOGINFO
 

The command in our case will return 86 records as follows:

在本例中，该命令将返回86条记录，如下所示：

This excessive number of VLFs is mainly generated due to growing the database transaction log very frequently and in very small increments. To overcome that issue, you should perform a transaction log backup on your database, shrink the transaction log as much as possible and finally specify an initial size of the transaction log file large enough to handle the database workload, without the need for frequent growth. Checking the number of VLFs again after performing the transaction log backup and the shrink operations:

VLF数量过多的主要原因是非常频繁且以很小的增量增长数据库事务日志。 为了解决该问题，您应该在数据库上执行事务日志备份，尽可能缩小事务日志，最后指定事务日志文件的初始大小，该大小足以应付数据库工作负载，而无需频繁增长。 执行事务日志备份和收缩操作后，再次检查VLF的数量：

 
USE SQLShackDemo
GO
DBCC LOGINFO
 

The number will be decreased to 35 as in the result below:

结果将减少到35，如下所示：

恢复中 (RECOVERY PENDING)

Having your database stuck in RECOVERY PENDING state, means that the database recovery process failed, due to missing files or potentially for resource-related reasons, preventing the database from being recovered successfully, but that the database is not damaged. In this case, the database will be unavailable for the user access and required a further user action to resolve the error and let the recovery process be completed successfully.

使数据库停留在RECOVERY PENDING状态意味着数据库恢复过程由于文件丢失或与资源相关的原因而失败，从而阻止了数据库的成功恢复，但是数据库并未受到损坏。 在这种情况下，该数据库将不可用于用户访问，并且需要进一步的用户操作来解决该错误，并使恢复过程成功完成。

The special word “Recovery Pending” between brackets beside the database name, as shown, indicates that the database is in a RECOVERY PENDING state:

如图所示，在数据库名称旁边的方括号之间的特殊字词“ Recovery Pending”表示数据库处于RECOVERY PENDING状态：

The SQL Server error log is the best place from which you can start your investigation. In our case, the error log shows that the database has not recovered successfully due to a database missing file that may be deleted or renamed:

SQL Server错误日志是您可以开始调查的最佳位置。 在我们的例子中，错误日志显示由于数据库丢失的文件（可能会被删除或重命名）而未能成功恢复数据库：

Locating the missing file again, taking the database offline and brining it online, the database will be recovered completely as shown in the error log event below:

再次找到丢失的文件，使数据库脱机并使其联机联机，数据库将完全恢复，如下面的错误日志事件所示：

疑似 (SUSPECT)

A database that is in the SUSPECT states means that the database is not available for user access. In this database state, the database recovery process has started but not completed successfully, requiring further user action in order to fix that issue and repair the corrupted files. SQL Server marks a database as SUSPECT due to many reasons, such as database files corruption, unavailable database files or improper shutdown of the SQL database server while running a big transaction.

处于“可疑”状态的数据库意味着该数据库不可用于用户访问。 在此数据库状态下，数据库恢复过程已开始但未成功完成，需要用户采取进一步的措施才能解决该问题并修复损坏的文件。 由于多种原因（例如数据库文件损坏，数据库文件不可用或在运行大事务时关闭SQL数据库服务器不正确），SQL Server将数据库标记为“可疑”。

Let us simulate a database corruption situation in which the SQL Server will mark the database as SUSPECT. We will create a new testing database, create a simple table in that database:

让我们模拟一个数据库损坏的情况，在这种情况下，SQL Server会将数据库标记为SUSPECT。 我们将创建一个新的测试数据库，在该数据库中创建一个简单表：

 
USE [master]
GO
 CREATE DATABASE [SuspectDBDemo]
GO
 
 USE [SuspectDBDemo]
GO
 CREATE TABLE [Employees] (
    [ID] INT,
    [FirstName]   VARCHAR (50),
    [LastName]    VARCHAR (50),
    [Address] NVARCHAR (MAX));
GO
 

Moreover, we will fill that table with 1000 records using the ApexSQL Generate, synthetic test data tool as follows:

此外，我们将使用ApexSQL Generate合成测试数据工具，用1000条记录填充该表，如下所示：

What we will do is start a transaction that will update the testing table without committing it, and perform a CHECKPOINT command to write it to the disk.

我们要做的是启动一个事务，该事务将在不提交测试表的情况下对其进行更新，然后执行CHECKPOINT命令将其写入磁盘。

 
BEGIN TRAN
UPDATE
    [Employees] SET [Address] = 'AMM' WHERE [LastName] like 'Evans';
GO
CHECKPOINT
GO
 

At the same time from another session, we will perform a SHUTDOWN command to terminate the SQL Server process:

在另一个会话的同时，我们将执行SHUTDOWN命令来终止SQL Server进程：

 
SHUTDOWN WITH NOWAIT;
GO
 

After that, we will open the database log file using any hex editor, and modify the first section by filling it with zeros and save the file again as below:

之后，我们将使用任何十六进制编辑器打开数据库日志文件，并用零填充来修改第一部分，然后再次保存该文件，如下所示：

Finally, we will start the SQL Server service again. If you try to run any simple query on that database, an error showing that the database is inaccessible will be displayed as follows:

最后，我们将再次启动SQL Server服务。 如果您尝试在该数据库上运行任何简单查询，则将显示以下错误消息，表明该数据库不可访问：

Checking the database state using the DATABASEPROPERTYEX function:

使用DATABASEPROPERTYEX函数检查数据库状态：

 
SELECT DATABASEPROPERTYEX ('SuspectDBDemo', 'STATUS') AS DatabaseStatus
 

The result will show that the database is in SUSPECT state:

结果将显示数据库处于SUSPECT状态：

The special word, “Suspect” between brackets beside the database name, as shown below, indicates that the database is in SUSPECT state:

数据库名称旁边方括号之间的特殊字词“ Suspect”（如下所示）表示数据库处于“可疑”状态：

Again, and always, referring to the SQL Server error log will help you finding the root cause of the database SUSPECT issue, which is an issue with the log file in our case as follows:

同样，并且总是，参考SQL Server错误日志将帮助您找到数据库SUSPECT问题的根本原因，在我们的情况下，这是日志文件的问题，如下所示：

紧急情况 (EMERGENCY)

The database can be changed to the EMERGENCY state by a sysadmin user action, in order to safely perform database maintenance or for troubleshooting purposes. In this state, the database will be in single-user mode to be repaired or restored, marked as READ_ONLY where you can export the data out of the database, logging is disabled and the access is restricted only to the sysadmin role members.

可以通过sysadmin用户操作将数据库更改为EMERGENCY状态，以安全地执行数据库维护或用于故障排除。 在这种状态下，数据库将以单用户模式进行修复或还原，标记为READ_ONLY，您可以在其中将数据导出数据库，禁用日志记录，并且访问权限仅限于sysadmin角色成员。

Let us get back again to the previous SuspectDBDemo corrupted database that is marked as SUSPECT. In order to troubleshoot its problem and resolve it, we will change the database state to EMERGENCY, permitting the sysadmin users read-only access to that database. The below ALTER DATABASE statement is used to set the database state to EMERGENCY:

让我们再次回到标记为SUSPECT的先前的SuspectDBDemo损坏的数据库。 为了解决它的问题并解决它，我们将数据库状态更改为EMERGENCY，以允许sysadmin用户以只读方式访问该数据库。 下面的ALTER DATABASE语句用于将数据库状态设置为EMERGENCY：

 
ALTER DATABASE SuspectDBDemo SET EMERGENCY
GO
 

The special word “Emergency” between brackets beside the database name, as shown below, indicates that the database is in EMERGENCY state:

数据库名称旁边方括号之间的特殊单词“ Emergency”（如下所示）表示数据库处于EMERGENCY状态：

Having the database in EMERGENCY state, we can work on resolving the problem safely. In order to check for database corruption, a DBCC CHECKDB command can be executed while the database in EMERGENCY state. Before doing that, the database should be changed explicitly to run using SINGLE_USER mode using the below ALTER DATABASE command:

使数据库处于紧急状态，我们可以安全地解决问题。 为了检查数据库是否损坏，可以在数据库处于紧急状态时执行DBCC CHECKDB命令。 在此之前，应使用以下ALTER DATABASE命令将数据库显式更改为以SINGLE_USER模式运行：

 
ALTER DATABASE SuspectDBDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 

The DBCC CHECKDB command can be executed now on that database, with the REPAIR_ALLOW_DATA_LOSS option. Damaged data and/or indexes may be removed to make the database physically consistent, but with possible data loss. In addition, the transaction log file will be rebuilt if there is any problem with that transaction log. The below DBCC CHECKDB command is used to fix the database corruption:

现在可以使用REPAIR_ALLOW_DATA_LOSS选项在该数据库上执行DBCC CHECKDB命令。 可能会删除损坏的数据和/或索引，以使数据库在物理上保持一致，但可能会丢失数据。 此外，如果该事务日志有任何问题，则将重建该事务日志文件。 下面的DBCC CHECKDB命令用于修复数据库损坏：

 
DBCC CHECKDB (SuspectDBDemo, REPAIR_ALLOW_DATA_LOSS)
GO
 

The clear DBCC CHECKDB command result in our case will be as follows:

在本例中，清除DBCC CHECKDB命令的结果如下：

Changing the database running mode back to MULTI_USER mode:

将数据库运行模式更改回MULTI_USER模式：

 
ALTER DATABASE SuspectDBDemo SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
 

The database transaction log file corruption issue is resolved now, after rebuilding the log file, and the database is ONLINE again and available for the user access:

重建日志文件后，数据库事务日志文件损坏问题现在已解决，并且该数据库再次处于联机状态，可供用户访问：

If you run a simple SELECT statement from the database table, the data will be retrieved with no issue:

如果您从数据库表中运行一个简单的SELECT语句，则将毫无问题地检索数据：

离线 (OFFLINE)

When the database is in OFFLINE state, the database is not functioning nor unavailable for the user access. The database state can be changed to or from OFFLINE state only by an explicit user action. Setting the database state to OFFLINE helps in migrating the database files to a new disk drive, or preventing the users from reaching it for any reason. The below ALTER DATABASE statement is used to change the database state to OFFLINE:

当数据库处于脱机状态时，该数据库将无法运行，也无法供用户访问。 只能通过明确的用户操作将数据库状态更改为OFFLINE状态或从OFFLINE状态更改为OFFLINE状态。 将数据库状态设置为OFFLINE有助于将数据库文件迁移到新的磁盘驱动器，或防止用户出于任何原因访问它。 下面的ALTER DATABASE语句用于将数据库状态更改为OFFLINE：

 
ALTER DATABASE [MDW] SET OFFLINE 
GO
 

The same action can be performed using the SQL Server Management Studio, right-clicking on the database -> Tasks and choose Take Offline task as follows:

可以使用SQL Server Management Studio执行相同的操作，右键单击数据库->任务，然后选择“执行脱机任务”，如下所示：

The red arrow and the Offline special word between brackets beside the database name indicates that the database is in OFFLINE state as follows:

数据库名称旁边方括号内的红色箭头和“脱机”特殊词表示数据库处于OFFLINE状态，如下所示：

Taking into consideration that the database will stay offline unless you perform an explicit action to bring it online. The below ALTER DATABASE statement will bring the database online back:

考虑到数据库将保持脱机状态，除非您执行显式操作使其联机。 下面的ALTER DATABASE语句将使数据库重新联机：

 
ALTER DATABASE [MDW] SET ONLINE
GO
 

Bringing the database online using the SQL Server Management Studio can be performed by, right-clicking on the database -> Tasks and choose Bring Online task:

使用SQL Server Management Studio将数据库联机，可以通过右键单击数据库->任务，然后选择“联机任务”来执行：

结论 (Conclusion)

In this article, we’ve described seven different states of a SQL Server database, showed how a database operated in these states and how to move the database from one state to another.

在本文中，我们描述了SQL Server数据库的七个不同状态，展示了数据库如何在这些状态下运行以及如何将数据库从一种状态转移到另一种状态。

Changing the database state is critical and you should make sure that this change is performed at the right time, in a correct situation, and that you have the recovery plan in the case of failure.

更改数据库状态至关重要，您应该确保在正确的时间，正确的情况下执行此更改，并且在发生故障的情况下具有恢复计划。

翻译自: https://www.sqlshack.com/understanding-different-sql-server-database-states/