配置恢复间隔服务器配置选项Configure the recovery interval Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明了如何使用 SQL ServerSQL Server 中配置 SQL Server Management StudioSQL Server Management Studio “恢复间隔” Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the recovery interval server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. “恢复间隔” 选项定义恢复某一数据库所需时间的上限。The recovery interval option defines an upper limit on the time recovering a database should take. SQL Server 数据库引擎SQL Server Database Engine 使用为该选项指定的值确定 自动检查点 对给定数据库发出的大致频率。The SQL Server 数据库引擎SQL Server Database Engine uses the value specified for this option to determine approximately how often to issue automatic checkpoints on a given database.

默认恢复间隔值为 0,这将允许 数据库引擎Database Engine 自动配置恢复间隔。The default recovery-interval value is 0, which allows the 数据库引擎Database Engine to automatically configure the recovery interval. 通常,对于活动数据库,该默认恢复间隔将导致大约一分钟执行一次自动检查点检查,并且导致不到一分钟的恢复时间。Typically, the default recovery interval results in automatic checkpoints occurring approximately once a minute for active databases and a recovery time of less than one minute. 较高的值表示近似的最大恢复时间,以分钟为单位。Higher values indicate the approximate maximum recovery time, in minutes. 例如,将恢复间隔设置为 3 指示最大恢复时间大约为 3 分钟。For example, setting the recovery interval to 3 indicates a maximum recovery time of approximately three minutes.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 恢复间隔仅影响使用默认目标恢复时间 (0) 的数据库。The recovery interval affects only databases that use the default target recovery time (0). 若要覆盖数据库上的服务器恢复间隔,请对该数据库配置非默认目标恢复时间。To override the server recovery interval on a database, configure a non-default target recovery time on the database. 有关详细信息,请参阅 更改数据库的目标恢复时间 (SQL Server)服务器配置选项。For more information, see Change the Target Recovery Time of a Database (SQL Server).

建议Recommendations

  • 此选项是一个高级选项,仅应由有经验的数据库管理员或认证的 SQL ServerSQL Server 专业人员更改。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional.

  • 通常,我们建议您将恢复间隔保持为 0,除非您遇到了性能问题。Typically, we recommend that you keep the recovery interval at 0, unless you experience performance problems. 如果您决定增大恢复间隔设置,我们建议一点一点逐渐增大该值并评估每次增大对恢复性能的影响。If you decide to increase the recovery-interval setting, we recommend increasing it gradually by small increments and evaluating the effect of each incremental increase on recovery performance.

  • 如果您使用 sp_configure“恢复间隔” 选项的值更改为超过 60(分钟),则指定 RECONFIGURE WITH OVERRIDE。If you use sp_configure to change the value of the recovery interval option to more than 60 (minutes), specify RECONFIGURE WITH OVERRIDE. WITH OVERRIDE 将禁用配置值检查(检查无效的值或并非推荐的值)。WITH OVERRIDE disables configuration value checking (for values that are not valid or are nonrecommended values).

SecuritySecurity

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

设置恢复间隔To set the recovery interval

  1. 在对象资源管理器中,右键单击服务器实例,再选择 “属性”In Object Explorer, right-click server instance and select Properties.

  2. 单击 “数据库设置” 节点。Click the Database settings node.

  3. “恢复” 下的 “恢复间隔(分钟)” 框中,键入或选择一个介于 0 到 32767 之间的值,以设置 SQL ServerSQL Server 在启动时用于恢复每个数据库花费的最长时间(分钟)。Under Recovery, in the Recovery interval (minutes) box, type or select a value from 0 through 32767 to set the maximum amount of time, in minutes, that SQL ServerSQL Server should spend recovering each database at startup. 默认值为 0,指示由 SQL ServerSQL Server自动配置。The default is 0, indicating automatic configuration by SQL ServerSQL Server. 实际上,这表示每个数据库的恢复时间不超过 1 分钟,对于活动的数据库大约每 1 分钟有一个检查点。In practice, this means a recovery time of less than one minute and a checkpoint approximately every one minute for active databases.

使用 Transact-SQLUsing Transact-SQL

设置恢复间隔To set the recovery interval

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何使用 sp_configurerecovery interval 选项的值设置为 3 分钟。This example shows how to use sp_configure to set the value of the recovery interval option to 3 minutes.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'recovery interval', 3 ;  
GO  
RECONFIGURE;  
GO  
  

有关详细信息,请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。For more information, see Server Configuration Options (SQL Server).

跟进:在配置恢复间隔选项之后Follow Up: After you configure the recovery internal option

该设置将立即生效,无需重新启动服务器。The setting takes effect immediately without restarting the server.

另请参阅See Also

更改数据库的目标恢复时间 (SQL Server) Change the Target Recovery Time of a Database (SQL Server)
数据库检查点 (SQL Server) Database Checkpoints (SQL Server)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
show advanced options 服务器配置选项 show advanced options Server Configuration Option
RECONFIGURE (Transact-SQL)RECONFIGURE (Transact-SQL)