SQL Server 2012还原一直卡在ASYNC_IO_COMPLETION浅析

在SQL
Server
2012(11.0.7001.0)下面在还原一个数据库(备份文件40多G大小,实际数据库大小300G),在还原过程中,出现一直等待ASYNC_IO_COMPLETION,如下测试截图所示,已经等待了72分钟了,但是还原比例依然为0%

SQL Server 2012 日志传送

 

一、准备

SELECT  r.session_id ,

        r.command ,

        r.start_time,

        r.status,

        r.wait_type,

        CONVERT(NUMERIC(6, 2), r.percent_complete)   AS [Percent Complete(%)] ,

        CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,

                                     GETDATE()), 20) AS [ETA Completion Time] ,

        CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,

        CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,

        CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0

        / 60.0) AS [ETA Hours] ,

        CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,

                                                  r.statement_start_offset / 2,

                                                  CASE WHEN r.statement_end_offset = -1

                                                       THEN 1000

                                                       ELSE ( r.statement_end_offset

                                                              - r.statement_start_offset )

                                                            / 2

                                                  END)

                                 FROM   sys.dm_exec_sql_text(sql_handle)

                               )) AS CommandText

FROM    sys.dm_exec_requests r

WHERE   command IN ( 'RESTORE DATABASE', 'BACKUP DATABASE','RESTORE LOG' );

数据库为完全恢复模式,并事先做一次完全备份。

 

共享一个文件夹,主机备份放在这个文件夹,而且客户机有权访问这个共享文件夹。

 

 

 

二、基本配置

图片 1

1、启动配置向导

 

图片 2

当然,这里是实验,如果还要继续等待的话,相信这个时间会更长。这个是比较让人奇怪的现象。后面查了一下这个跟即时文件初始化(Instant
File Initialization
(IFI))有关。关于这个概念,可以参考官方文档数据库文件初始化
,摘抄部分内容如下所示:

2、配置事务日志备份计划

 

  在“备份文件夹的网络路径”框中,键入为事务日志备份文件夹创建的共享的网络路径。

 

  如果备份文件夹位于主服务器上,在“如果备份文件夹位于主服务器上,则键入该文件夹的本地路径”框中键入该备份文件夹的本地路径。(如果备份文件夹不在主服务器上,此框可以保留为空。)

 

  重要提示:如果主服务器上的 SQL Server
服务帐户运行在本地系统帐户下,则必须在主服务器上创建备份文件夹,并指定该文件夹的本地路径。

数据库文件初始化

图片 3

 

  SQL Server 2008 Enterprise
支持备份压缩。创建日志传送配置时,可以通过选择以下选项之一来控制日志备份的备份压缩行为:“使用默认服务器设置”、“压缩备份”或“不压缩备份”。

初始化数据和日志文件以覆盖之前删除的文件遗留在磁盘上的任何现有数据。 执行以下其中一项操作时,应首先通过零填充(用零填充)数据和日志文件来初始化这些文件:

  在本例中,应事先共享一个 \sql1sqlbackup 这个文件夹,本机的SQL
Server
Agent会自动创建一个作业,将事务日志备份到这个共享文件夹。作业计划如下图所示。

 

图片 4

·        
创建数据库。

3、添加辅助服务器实例

·        
向现有数据库添加数据或日志文件。

(1)初始化辅助数据库

·        
增大现有文件的大小(包括自动增长操作)。

  “辅助服务器实例”显示日志传送配置中当前配置为辅助服务器的 SQL Server
实例的名称。

·        
还原数据库或文件组。

 
 “辅助数据库”显示日志传送配置的辅助数据库名称。将新的辅助数据库添加到日志传送配置时,可以从列表中选择数据库或在该框中键入新数据库的名称。如果
输入新数据库的名称,则必须在“初始化”选项卡上选择一个选项,该选项卡可将主数据库的完整数据库备份还原到辅助数据库中。新数据库将作为还原操作的一部
分进行创建。

 

  上述选项配置完成之后,点“连接”按钮以连接到日志传送配置中用作辅助服务器的
SQL Server 实例。用于连接的帐户必须是辅助服务器实例上 sysadmin
固定服务器角色的成员。

文件初始化会导致这些操作花费更多时间。 但是,首次将数据写入文件后,操作系统就不必用零来填充文件

图片 5

 

  • 是,生成主数据库的完整备份并将其还原到辅助数据库

 

  通过备份主数据库并在辅助服务器上还原该数据库,让 SQL Server
Management Studio
配置辅助数据库。如果在“辅助数据库”框中输入新的数据库名称,数据库将作为还原操作的一部分进行创建。

即时文件初始化 (IFI)

  • 是,将主数据库的现有备份还原到辅助数据库

 

  让 Management Studio
使用主数据库的现有备份初始化辅助数据库。在“备份文件”框中键入该备份的位置。如果在“辅助数据库”框中输入新的数据库名称,数据库将作为还原操作的一部分进行创建。

在SQL
Server中,可以在瞬间对数据文件进行初始化,以避免零填充操作。即时文件初始化可以快速执行上述文件操作。 即时文件初始化功能将回收使用的磁盘空间,而无需使用零填充空间。 相反,新数据写入文件时会覆盖磁盘内容。 日志文件不能立即初始化。

  如果选择“是,将主数据库的现有备份还原到辅助数据库”选项,请键入要用于初始化辅助数据库的完整数据库备份的路径和文件名。

 

  • 否,辅助数据库已初始化

备注

  指定辅助数据库已初始化并准备接受主数据库的事务日志备份。如果在“辅助数据库”框中键入新的数据库名称,则此选项不可用。

只有在 Microsoft Windows
XP Professional 或 Windows
Server 2003 或更高版本中才可以使用即时文件初始化功能。

 

 

(2)复制文件

重要

 
 键入事务日志备份应复制到的路径以还原到辅助数据库。通常,此路径为辅助服务器上文件夹的本地路径。但是,如果该文件夹位于其他服务器,则必须指定该文
件夹的 UNC 路径。辅助服务器实例的 SQL Server
服务帐户必须具有此文件夹的读取权限。此外,还必须向代理帐户授予此网络共享的读写权限。通过代理帐户,复制作业和还原作业将在辅助服务器实例上的该帐户
下运行。默认情况下,这是辅助服务器实例的 SQL Server 代理服务帐户,但是
sysadmin 可以为该作业选择其他代理帐户。

只有在数据文件中才可以使用即时文件初始化功能。 创建日志文件或其大小增长时,将始终零填充该文件。

图片 6

 

  在本例中,应事先创建一个 C:test 这个文件夹。本机的 SQL Server
Agent 会自动创建一个作业,定时将 \sql1sqlbackup
文件夹中的事务日志备份 copy 到 C:test 。

即时文件初始化功能仅在向SQL
Server服务启动帐户授予了 SE_MANAGE_VOLUME_NAME
之后才可用。 Windows
Administrator 组的成员拥有此权限,并可以通过将其他用户添加到 执行卷维护任务 安全策略中来为其授予此权限。

  请注意SQL Server Agent服务的启动帐户,以免发生权限问题。

 

(3)还原事务日志

重要

图片 7

某些功能使用(如透明数据加密
(TDE))可以阻止即时文件初始化。

  本机的 SQL Server Agent 会自动创建一个作业,定时从 C:test
读取日志备份文件,然后恢复。

 

 

 

4、完成配置

 因为这个案例中,启动SQL
Server的服务启动账号为NT
ServiceMSSQLSERVER,所以没有权限即时文件初始化的,如果将启动SQL
Server的服务启动账号改为具有管理员权限的域账号,就不会出现这种情况。另外,如果一定要以NT
ServiceMSSQLSERVER为启动账号,可以按如下步骤操作:

  完成上述配置之后,点“确定”即开始日志传送。

 

图片 8

要向一个帐户授予 Perform
volume maintenance tasks 权限:

 

 

三、备用模式

 

  默认设置时,数据库处于 NORECOVERY(无恢复)
模式,则不能运行查询。可以手动选择,使辅助数据库处于 STANDBY
(备作)模式,此时可以通过使用辅助服务器进行只读查询处理,可以减少主服务器的负荷。

  1. 在将要创建备份文件的计算机上打开本地安全策略应用程序 (secpol.msc)。

图片 9

 

图片 10

  1. 在左侧窗格中,展开“本地策略”
    ,然后单击“用户权限指派”

  配置使辅助数据库处于备用模式时,“在还原备份时断开数据库中用户的连接”选项指定了两种方式:

 

(1)还原事务日志备份时,使数据库用户断开连接。

  1. 在右侧窗格中,双击“执行卷维护任务”。

  如果选中此选项,则日志传送还原作业每次尝试将事务日志还原到辅助数据库时,用户都将与数据库断开连接。断开连接将按为还原作业设置的计划发生。

 

(2)不与用户断开连接。

  1. 单击“添加用户或组”
    ,添加用于备份的任何用户帐户。

  在这种情况下,如果用户连接到辅助数据库,则还原作业无法将事务日志备份还原到辅助数据库。事务日志备份将一直累积到没有用户连接到该数据库为止。

 

  重要提示:如果更改现有辅助数据库的恢复模式(例如,从“无恢复”模式到“备用”模式),则更改仅在下一次日志备份还原到数据库后才会生效。

  1. 单击“应用”
    ,然后关闭所有“本地安全策略”
    对话框。

 

 

四、补充说明

图片 11

1、还原选项

 

   客户机默认将数据库恢复到系统默认的路径下,例如: C:Program
FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA
。若要将辅助数据库的数据和日志文件还原到辅助服务器上的非默认位置,请在“初始化辅助数据库”选项卡中单击此按钮。

 

  单击此按钮将打开“还原选项”对话框。在该对话框中,可以指定非默认文件夹的路径,用于驻留辅助数据库及其日志。如果指定其中的一个文件夹,则必须指定这两个路径。

设置后,重启SQL
Server服务,然后还原数据库就会正常化,此时的等待事件为BACKUPTHREAD,而不是ASYNC_IO_COMPLETION,
如果版本是SQL Server 2012
SP4或以上版本,可以通过下面SQL查看识别是否启用了即时文件初始化

  这些路径必须引用辅助服务器上的本地驱动器。另外,这些路径必须以本地驱动器号和冒号开头(例如,C:)。映射的驱动器号或网络路径无效。

 

  如果单击“还原选项”按钮后决定使用默认文件夹,建议取消“还原选项”对话框。如果已经指定非默认位置,但现在要使用默认位置,请再次单击“还原选项”,清除文本框,再单击“确定”。

 

2、加速复制数据库

SELECT  servicename ,

        startup_type ,

        instant_file_initialization_enabled

FROM    sys.dm_server_services; 

  如果数据量较大,建议:先通过移动介质,将主机做的完全备份拿到客户机上恢复,然后再做日志传送。

 

 

 

3、监视日志传送

图片 12

  监视日志传送的最简单的方法是利用SQL Server
代理作业历史。在任何时间,只要怀疑一个服务器在日志传送方面存在问题,就应当首先检查这些作业,以确定问题的根本原因。

 

  另一种方式是执行状态报表。主服务器、辅助服务器、监视服务器的实例上单击右键,依次选择“报表”、“标准报表”、“事务日志传送状态”。

另外,从SQL
Server 2016 (13.x) 开始,可在安装期间授予服务帐户此权限。 如果使用命令提示符安装,请添加
/SQLSVCINSTANTFILEINIT 参数,或选中安装向导中“授予
SQL Server 数据库引擎服务执行卷维护任务权限”复选框。

 

 

原文出自:http://jimshu.blog.51cto.com/3171847/590413/

 

 

参考资料:

 

https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2017

发表评论

电子邮件地址不会被公开。 必填项已用*标注