設定 recovery interval 伺服器組態選項Configure the recovery interval Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否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 recovery interval 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. recovery interval 選項會定義資料庫的復原時間上限。The recovery interval option defines an upper limit on the time recovering a database should take. SQL Server Database EngineSQL Server Database Engine 會使用此選項的指定值,來決定要在指定資料庫上發出自動檢查點的大約頻率。The SQL Server Database EngineSQL 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 EngineDatabase Engine 自動設定復原間隔。The default recovery-interval value is 0, which allows the Database EngineDatabase 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_configurerecovery interval 選項設為大於 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).

安全性Security

權限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. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。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. 實務上,這表示復原時間小於一分鐘,而且使用中資料庫幾乎每分鐘有一次檢查點。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 EngineDatabase EngineConnect to the Database EngineDatabase 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 Server Configuration Option
RECONFIGURE (Transact-SQL)RECONFIGURE (Transact-SQL)