sp_change_log_shipping_secondary_database (Transact-SQL)

APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Changes secondary database settings.

Topic link icon Transact-SQL Syntax Conventions


[ @secondary_database = ] 'secondary_database',  
[, [ @restore_delay = ] 'restore_delay']  
[, [ @restore_all = ] 'restore_all']  
[, [ @restore_mode = ] 'restore_mode']  
[, [ @disconnect_users = ] 'disconnect_users']  
[, [ @block_size = ] 'block_size']  
[, [ @buffer_count = ] 'buffer_count']  
[, [ @max_transfer_size = ] 'max_transfer_size']  
[, [ @restore_threshold = ] 'restore_threshold']   
[, [ @threshold_alert = ] 'threshold_alert']   
[, [ @threshold_alert_enabled = ] 'threshold_alert_enabled']   
[, [ @history_retention_period = ] 'history_retention_period']  


[ @restore_delay = ] 'restore_delay' The amount of time, in minutes, that the secondary server waits before restoring a given backup file. restore_delay is int and cannot be NULL. The default value is 0.

[ @restore_all = ] 'restore_all' If set to 1, the secondary server restores all available transaction log backups when the restore job runs. Otherwise, it stops after one file has been restored. restore_all is bit and cannot be NULL.

[ @restore_mode = ] 'restore_mode' The restore mode for the secondary database.

0 = restore log with NORECOVERY.

1 = restore log with STANDBY.

restore is bit and cannot be NULL.

[ @disconnect_users = ] 'disconnect_users' If set to 1, users is disconnected from the secondary database when a restore operation is performed. Default = 0. disconnect_users is bit and cannot be NULL.

[ @block_size = ] 'block_size' The size, in bytes, that is used as the block size for the backup device. block_size is int with a default value of -1.

[ @buffer_count = ] 'buffer_count' The total number of buffers used by the backup or restore operation. buffer_count is int with a default value of -1.

[ @max_transfer_size = ] 'max_transfer_size' The size, in bytes, of the maximum input or output request which is issued by SQL Server to the backup device. max_transfersize is int and can be NULL.

[ @restore_threshold = ] 'restore_threshold' The number of minutes allowed to elapse between restore operations before an alert is generated. restore_threshold is int and cannot be NULL.

[ @threshold_alert = ] 'threshold_alert' Is the alert to be raised when the restore threshold is exceeded. threshold_alert is int, with a default of 14420.

[ @threshold_alert_enabled = ] 'threshold_alert_enabled' Specifies whether an alert will be raised when restore_thresholdis exceeded. 1 = enabled; 0 = disabled. threshold_alert_enabled is bit and cannot be NULL.

[ @history_retention_period = ] 'history_retention_period' Is the length of time in minutes in which the history will be retained. history_retention_period is int. A value of 1440 will be used if none is specified.

Return Code Values

0 (success) or 1 (failure)

Result Sets



sp_change_log_shipping_secondary_database must be run from the master database on the secondary server. This stored procedure does the following:

  1. Changes the settings in the log_shipping_secondary_database records as necessary.

  2. Changes the local monitor record in log_shipping_monitor_secondary on the secondary server using supplied arguments, if necessary.

We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. You can click the This page button in the Feedback section at the bottom of this page. We read every item of feedback about SQL, typically the next day. Thanks.


Only members of the sysadmin fixed server role can run this procedure.


This example illustrates using sp_change_log_shipping_secondary_database to update secondary database parameters for the database LogShipAdventureWorks.

EXEC master.dbo.sp_change_log_shipping_secondary_database   
 @secondary_database =  'LogShipAdventureWorks'  
,  @restore_delay = 0  
,  @restore_all = 1  
,  @restore_mode = 0  
,  @disconnect_users = 0  
,  @threshold_alert = 14420  
,  @threshold_alert_enabled = 1  
,  @history_retention_period = 14420;  

See Also

About Log Shipping (SQL Server)
System Stored Procedures (Transact-SQL)