数据库即时搜索_加速数据库恢复； 即时回滚和数据库恢复

数据库即时搜索

Accelerated database recovery will be the topic of this article, including killing an active query, abnormal shutdown and the accelerate recovery feature itself, in SQL 2019

在SQL 2019中，加速数据库恢复将是本文的主题，包括杀死活动查询，异常关闭和加速恢复功能本身

SQL Server Database recovery is an essential and critical task for the DBA. We take regular database backups to recover databases from any unexpected downtime. We face many scenarios where DBAs do not have control over the actual recovery, and the only solution is to wait for recovery to finish. In this article, we will discuss about SQL Server database recovery scenario along with new feature in SQL Server 2019 Accelerated Database Recovery.

SQL Server数据库恢复对于DBA来说是一项至关重要的任务。 我们进行常规的数据库备份，以从任何意外停机中恢复数据库。 我们面对许多DBA无法控制实际恢复的情况，唯一的解决方案是等待恢复完成。 在本文中，我们将讨论有关SQL Server数据库恢复方案以及SQL Server 2019 加速数据库恢复中的新功能。

We will first prepare the environment and then explain the recovery issues. In this example I am using SQL Server 2019). You can verify instance version using the select @@Version command.

我们将首先准备环境，然后解释恢复问题。 在此示例中，我使用SQL Server 2019）。 您可以使用select @@ Version命令来验证实例版本。

Create a sample table using the following query

使用以下查询创建样本表

USE [SQLShackDemo]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[tblSQLShackDemo](
  [S.No.] [int] IDENTITY(0,1) NOT NULL,
  [value] [uniqueidentifier] NULL,
  [Date] [datetime] NULL
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[tblSQLShackDemo] ADD  DEFAULT (getdate()) FOR [Date]
GO

方案1：杀死活动的运行查询 (Scenario 1: Kill an active running query)

Suppose you are running a large insert or updates DML query. A query is in executing state but due to some reasons such as high CPU or memory consumption, blocking, deadlock, database performance issues you need to KILL it. Once you execute the Kill command, the query goes into RollBack state, and it might take a long time to complete the recovery process.

假设您正在运行大型插入或更新DML查询。 查询处于执行状态，但是由于某些原因（例如CPU或内存消耗高，阻塞，死锁，数据库性能问题），您需要将其杀死。 一旦执行Kill命令，查询将进入RollBack状态，并且可能需要很长时间才能完成恢复过程。

We are inserting 500K records into the tblSQLShackDemo table to demonstrate this issue. Execute the following query to begin a transaction.

我们将500K条记录插入到tblSQLShackDemo表中，以演示此问题。 执行以下查询以开始事务。

Begin transaction
Declare @Id int
Set @Id = 1
 
While @Id <= 1000000
Begin 
   Insert Into tblSQLShackDemo(value) values (newid())
   Set @Id = @Id + 1
End 

Once we executed the query, we can check its status using sp_who2 ‘SPID’ command.

一旦执行了查询，就可以使用sp_who2'SPID'命令检查其状态。

While the query is still executing, we can check the table count using NoLock hint along with our table name.

当查询仍在执行时，我们可以使用NoLock提示以及表名来检查表计数。

select count(*) from tblSQLShackDemo(nolock)

It is executing from 3 minutes and inserted 457134 records until now.

它从3分钟开始执行，到目前为止已插入457134条记录。

Now, we need to kill the SPID to start the rollback process. Execute the command KILL 55. In this command 55 is the SPID in which insert query is running.

现在，我们需要终止SPID以开始回滚过程。 执行命令KILL55。此命令55是在其中运行插入查询的SPID。

In the sp_who2 command, we can see the status of the query as ROLLBACK.

在sp_who2命令中，我们可以将查询状态视为ROLLBACK 。

We can track the progress of rollback command using the following query.

我们可以使用以下查询来跟踪rollback命令的进度。

KILL 55 with Statusonly

In the following screenshot, you can see it shows estimated rollback time is 3567 seconds that is approximately 60 minutes.

在以下屏幕截图中，您可以看到它显示估计的回滚时间为3567秒，大约为60分钟。

If the query goes longer before you kill it, it might take a few hours as well before the rollback completes. You need to bear the extra load in terms of CPU, Memory in rollback as well. It also blocks the current transactions on the particular table. We cannot do anything in this scenario except waiting to get it complete.

如果查询在终止之前进行了更长的时间，则回滚完成可能还需要几个小时。 您还需要承担CPU，回滚内存方面的额外负担。 它还会阻止特定表上的当前事务。 在这种情况下，我们只有等待完成它才能做任何事情。

方案2：查询运行时异常关闭 (Scenario 2: Abnormal shutdown while the query is running)

Let us imagine another scenario in which you started a transaction to insert a large number into our sample table. Suddenly the system crashed. Once the system is up, you need to start the SQL Services. SQL Server service is online. However, the user database is still performing recovery.

让我们想象一下另一个场景，在该场景中，您开始了一个事务，以将大量插入示例表中。 突然系统崩溃了。 系统启动后，您需要启动SQL Services。 SQL Server服务处于联机状态。 但是，用户数据库仍在执行恢复。

Once the SQL Server is back online, expand the databases. In the following screenshot, you can see that database status is In Recovery.

SQL Server重新联机后，展开数据库。 在以下屏幕截图中，您可以看到数据库状态为正在恢复 。

We cannot access the database at this time. We can check more details in the SQL Server Logs. In the logs, you get the following message.

我们目前无法访问数据库。 我们可以在SQL Server日志中检查更多详细信息。 在日志中，您将收到以下消息。

Recovery of database ‘SQLShackDemo’ (5) is 0% complete (approximately 36351 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

数据库'SQLShackDemo'（5）的恢复已完成0％（剩余大约36351秒）。 第2阶段，共3阶段。这仅是参考消息。 无需用户操作。

As per the error log entry, it will take approximately 36,351 seconds that is approximately 10 hrs. Really?! Do we need to wait for SQL Server database to come online for 10 hours? It is true. We need to wait for the database to come fully online. The worst part is that we cannot do anything apart from refreshing the error logs and monitor the progress. It is indeed a helpless condition for DBAs.

根据错误日志条目，将花费大约36,351秒（大约10个小时）。 真？！ 我们是否需要等待SQL Server数据库上线10个小时？ 是真的。 我们需要等待数据库完全联机。 最糟糕的是，除了刷新错误日志和监视进度之外，我们无法做任何其他事情。 对于DBA而言，确实是一个无奈的条件。

As per following screenshot recovery of database recovery phase 3 is started. At this point, database is available for the users. Wait, the database is accessible but SQL Server still making the recovery of the database.

按照以下屏幕快照，开始数据库恢复阶段3的恢复。 此时，数据库可供用户使用。 等待，数据库是可访问的，但是SQL Server仍在恢复数据库。

Once the database recovery is completed, we get the following message in the error log.

数据库恢复完成后，我们将在错误日志中获得以下消息。

Recovery completed for database SQLShackDemo (database ID 5) in 1802 second(s) (analysis 1375 ms, redo 551401 ms, undo 1246756 ms.) This is an informational message only. No user action is required.

数据库SQLShackDemo（数据库ID 5）的恢复已在1802秒（分析1375毫秒，重做551401毫秒，撤消1246756毫秒）中完成。这仅是参考消息。 无需用户操作。

SQL Server took 1802 seconds approximately 30 minutes to recover this database. It might take longer depending on the work SQL Server to do to bring database in a consistent state after recovery.

SQL Server花了1802秒大约30分钟的时间来恢复该数据库。 恢复数据库后，使数据库处于一致状态可能需要更长的时间，具体取决于SQL Server所做的工作。

We will cover more about recovery stages in the later part of the section.

我们将在本节的后面部分详细介绍恢复阶段。

We can see following pain points for DBA until now in SQL Server.

到目前为止，我们可以在SQL Server中看到DBA的以下痛点。

• Huge recovery time
  巨大的恢复时间
• Roll back takes longer time
  回滚需要更长的时间

Let us repeat these scenarios in the following step with the new feature of SQL Server 2019 Accelerated database Recovery.

让我们在下一步中使用SQL Server 2019 加速数据库恢复的新功能重复这些方案。

使用SQL Server 2019加速数据库恢复 (Accelerated Database Recovery with SQL Server 2019)

SQL Server 2019 introduced a new database recovery feature Accelerated Database Recovery. It redesigns the database recovery process in SQL Server. We can do an immediate rollback of any query. It also improves the database recovery in case of any disaster such as server crash, cluster or AG failover.

SQL Server 2019引入了新的数据库恢复功能Accelerated Database Recovery。 它重新设计了SQL Server中的数据库恢复过程。 我们可以立即回滚任何查询。 如果发生任何灾难，例如服务器崩溃，群集或AG故障转移，它还可以提高数据库恢复。

We need to enable the Accelerated Database Recovery feature at the database level. It is disabled by default for all databases.

我们需要在数据库级别启用加速数据库恢复功能。 默认情况下，所有数据库均禁用该功能。

In this example, we created another database SQLSHACKDEMO_ADR along with the same table tblSqlShackDemo.

在此示例中，我们创建了另一个数据库SQLSHACKDEMO_ADR以及同一表tblSqlShackDemo 。

We get a new column in sys.databases to check whether Accelerated Database Recovery is enabled or not on a particular database.

我们在sys.databases中获得一个新列，以检查特定数据库上是否启用了Accelerated Database Recovery 。

select name,create_date,compatibility_level ,physical_database_name,is_accelerated_database_recovery_on  from sys.databases

Enable Accelerated Database Recovery using following alter database command

使用以下alter database命令启用加速数据库恢复

ALTER DATABASE SQLSHACKDEMO_ADR SET ACCELERATED_DATABASE_RECOVERY = ON;

It took approx 7 minutes for me to enable this feature on a blank database. It might get improved in future releases of SQL Server 2019.

我花了大约7分钟的时间在空白数据库上启用此功能。 在将来SQL Server 2019版本中可能会得到改进。

Now, run the sys.database command mentioned above. In the following screenshot, we can see that Accelerated Database Recovery is enabled for SQLShackDemo_ADR database.

现在，运行上面提到的sys.database命令。 在以下屏幕截图中，我们可以看到已为SQLShackDemo_ADR数据库启用了Accelerated Database Recovery 。

Let us perform both the scenario with this Accelerated Database Recovery enabled database.

让我们使用启用了Accelerated Database Recovery的数据库执行两种方案。

方案1：杀死活动的运行查询 (Scenario 1: Kill an active running query)

Run the query to insert bulk records in tblSQLShackDemo table and Kill the session after approximately 3 minutes.

运行查询以将批量记录插入tblSQLShackDemo表中，并在大约3分钟后终止会话。

Here is the difference

这是区别

• Accelerated Database Recovery database took approximately 60 minutes to finish rollback. 加速数据库恢复进行回滚大约需要60分钟才能完成回滚。
• Accelerated Database Accelerated Database Recovery database performed the rollback quickly. Recovery进行回滚可以快速执行回滚。

方案2：查询运行时异常关闭 (Scenario 2: Abnormal shutdown while the query is running)

Let us repeat scenario 2 and restart the SQL Server while the query is still executing. Once the server is back, connect to the instance. We can see that the database is online now.

让我们重复方案2，并在查询仍在执行时重新启动SQL Server。 服务器返回后，连接到实例。 我们可以看到该数据库现在处于联机状态。

Yes, it is true. We do not wait for long to wait in a painful situation for refreshing the error logs and wait to see the message that database is online.

是的，它是真的。 在痛苦的情况下，我们不会等待很长时间才能刷新错误日志，也不会等待看到数据库联机的消息。

Let us go to the error log, and we get the following message.

让我们转到错误日志，然后得到以下消息。

Recovery completed for database SQLShackDemo_ADR (database ID 6) in 12 second(s) (analysis 8162 ms, redo 2593 ms, undo 236 ms.) This is an informational message only. No user action is required.

数据库SQLShackDemo_ADR（数据库ID 6）的恢复已在12秒内完成（分析8162 ms，重做2593 ms，撤消236 ms。）这仅是参考消息。 无需用户操作。

Here is the difference you can notice between both the executions.

您可以注意到这两个执行之间的区别。

In the following screenshot, you can notice the database recovery time difference in a graphical way.

在以下屏幕截图中，您可以通过图形方式注意到数据库恢复时间的差异。

In SQL Server, we have following three phases of database recovery.

在SQL Server中，我们具有数据库恢复的以下三个阶段。

1. Analysis
   分析
2. Redo
   重做
3. Undo
   撤消

In the following table, we can understand these three phases of recovery.

在下表中，我们可以了解恢复的这三个阶段。

1. Analysis Phase: SQL Server periodically runs the internal checkpoint process. When SQL Server starts, it starts reading the transaction log from the last successful checkpoint. It reads the log forward, rebuilds the transactions table, and dirty pages table. At the end of the analysis phase, we have either committed transaction (requires roll-forward) or uncommitted transaction (requires rollback) 分析阶段： SQL Server定期运行内部检查点过程。 SQL Server启动时，它将开始从上一个成功的检查点读取事务日志。 它读取日志，重建事务表和脏页表。 在分析阶段结束时，我们已经提交了事务（需要前滚）或未提交了事务（需要回滚）
2. Redo Phase: In this phase, SQL Server starts reading from the oldest uncommitted transaction and with the help of a dirty page table, it takes system at the point of the crash. SQL Server (from SQL Server 2005 onward) is accessible for the users after Redo phase 重做阶段：在此阶段中，SQL Server开始从最早的未提交事务中读取数据，并在脏页表的帮助下使系统崩溃。 重做阶段之后，用户可以访问SQL Server（从SQL Server 2005起）
3. Undo Phase: SQL Server needs to roll back all the active changes at the time of system crash. SQL Server starts reading transaction log in the backward direction and with the help of Active transaction table rolls back the transactions 撤消阶段： SQL Server需要在系统崩溃时回滚所有活动的更改。 SQL Server开始向后读取事务日志，并在活动事务表的帮助下回滚事务

When we kill an active transaction, SQL Server needs to perform Undo recovery process. Therefore, it might take a long time to roll back as well.

当我们终止活动事务时，SQL Server需要执行撤消恢复过程。 因此，回滚可能还需要很长时间。

In the following image (Reference – Microsoft Docs) shows the overall Database recovery process.

在下图中（参考– Microsoft Docs ）显示了整个数据库恢复过程。

SQL Server 2019中的加速数据库恢复 (Accelerated Database Recovery in SQL Server 2019)

Once we enabled Accelerated Database Recovery on a SQL Server Database, it stores the version of all modifications. It is similar to versioning in Read Committed Snapshot Isolation level. SQL Server stores the previous version in a secondary memory optimized log called s-log.

一旦我们在SQL Server数据库上启用了加速数据库恢复 ，它将存储所有修改的版本。 它类似于“ 读取已提交快照隔离”级别中的版本控制。 SQL Server将先前版本存储在名为s-log的辅助内存优化日志中。

• Persisted Version Store (PVS): In Persisted version store, SQL Server stores the row version in the database enabled with Accelerated Database Recovery Feature
• 永久版本存储（PVS）：在永久版本存储中，SQL Server将行版本存储在启用了“ 加速数据库恢复”功能的数据库中
• Logical Revert: SQL Server uses the PVS to undo the changes immediately, and it does not need to read the details from the transaction log that is a time-consuming process 逻辑还原： SQL Server使用PVS立即撤消更改，并且不需要从事务日志中读取详细信息，这是一个耗时的过程
• sLog: It stores log records for log records for non-versioned operations. These operations can be DDL command, bulk queries. It makes the redo and undo processing quicker because they only need to process non-versioned operations sLog：它存储用于非版本化操作的日志记录的日志记录。 这些操作可以通过DDL命令进行批量查询。 它使重做和撤消处理更快，因为它们只需要处理非版本化的操作
• Cleaner: Cleaner process automatically removes the version that is not required by SQL Server for the recovery 清除程序：清除程序会自动删除SQL Server进行恢复所不需要的版本

In the following image (Reference – Microsoft Docs) shows the overall Database recovery process with Accelerated Database Recovery.

在下图中（参考– Microsoft Docs ）显示了使用Accelerated Database Recovery的整个数据库恢复过程。

结论 (Conclusion)

In this article, we explored the SQL Server 2019 Accelerated Database Recovery feature. It improves the database recovery time and resolves DBA painful situations.

在本文中，我们探索了SQL Server 2019 加速数据库恢复功能。 它缩短了数据库恢复时间，并解决了DBA的痛苦情况。

目录 (Table of contents)

Accelerated Database Recovery; Instant Rollback and Database Recovery

Accelerated Database Recovery and Long Running Transactions with Transaction Log Growth

加速数据库恢复； 即时回滚和数据库恢复

通过事务日志增长加快数据库恢复和长期运行的事务

翻译自: https://www.sqlshack.com/accelerated-database-recovery-instant-rollback-and-database-recovery/

数据库即时搜索