SQL Server – Log Shipping – Teardown


Reviewing our SQL Server Error Log and noted an innumerable entries bearing “The log shipping primary database [server].[database] has backup threshold of N minutes and has not performed a backup log operation for [M] minutes. Check agent log and logshipping monitor information“.


Traced the error back to a log shipping alert that is configured for SQL Server Agent to run every 2 minutes.  The job’s  name is LSAlert_[servername].


Tear down Log Shipping


For those who like using the GUI, I will suggest to use SQL Server Management Studio, access the database, and access its properties.   Right click on the database and select “Transaction Log Shipping“.

In the Log Shipping tab, un-check “Enable this as a primary database in a log shipping configuration”.



Transact SQL

As the database that is erroring is no longer on our system, we can not process via GUI.

So like slaves to an age old trade, let us go write a bit of code.


    [ @primary_database = ] 'primary_database', 
    [ @secondary_server = ] 'secondary_server', 
    [ @secondary_database = ] 'secondary_database'

Sample – Prep

Review Parameters

Let us be careful and know exactly what values to pass in…

We know that we need to pass along the following parameters

  1. Primary Database
  2. Secondary Server
  3. Secondary Database


SQL Script:

use master

declare @server sysname
declare @database sysname

set @server = cast(serverproperty('servername') as sysname)
set @database = 'HRDB'

exec master.dbo.sp_help_log_shipping_monitor_primary
	  @primary_server = @server
	, @primary_database = @database





  1. We have the primary server and database
  2. The last time a backup occurred along with the name of the ensuing backup file
  3. But, nothing about the secondary server nor the destination database name


MSDB – log_shipping_primary_databases & log_shipping_primary_secondaries
SQL Script:

         , [primaryServer]
             = cast(serverproperty('servername') as sysname)
	 , tblLSPD.[primary_database]
	 , tblLSPD.[backup_job_id]
	 , tblLSPD.[monitor_server]
	 , tblLSPD.[last_backup_date]

	 , tblLSPS.secondary_server
	 , tblLSPS.secondary_database

  FROM [msdb].[dbo].[log_shipping_primary_databases] tblLSPD

	left outer join [dbo].[log_shipping_primary_secondaries] tblLSPS

	  on tblLSPD.[primary_id] = tblLSPS.[primary_id]




  1. There we have it
    • We have our primary Server and DB
    • And, our secondary Server and DB, as well


Sample – Actual


On Primary

  use [master]

  begin tran

     declare @primaryDatabase    sysname
     declare @secondaryServer    sysname
     declare @secondaryDatabase  sysname

     set @primaryDatabase = 'HRDB'
     set @secondaryServer = 'HRDBMIRR'
     set @secondaryDatabase = 'HRDB'

     select  = 'Before' , * 
     from msdb.dbo.log_shipping_primary_secondaries

     exec sp_delete_log_shipping_primary_secondary
           @primary_database = @primaryDatabase
         , @secondary_server = @secondaryServer 
         , @secondary_database = @secondaryDatabase

     select  = 'After' , * 
     from msdb.dbo.log_shipping_primary_secondaries

     exec sp_delete_log_shipping_primary_database
		@database = @primaryDatabase

  rollback tran


On Secondary

 use master

 declare @database sysname

  begin tran
     set @database = 'HRDB'

      exec sp_delete_log_shipping_secondary_database
         @secondary_database = @database

  rollback tran



Error Messages

Error – Primary SQL Server Instance has active shipping secondary databases

On the primary, If you issue sp_delete_log_shipping_primary_database without first ensuring that issuing sp_delete_log_shipping_primary_secondary worked successfully on the primary SQL Instance, you will get the error pasted below:

Msg 32011, Level 16, State 1, Procedure sp_delete_log_shipping_primary_database, Line 53
Primary Database  has active log shipping secondary database(s).
Drop the secondary database(s) first.

The error is raised due to the check for a matching entry in the msdb.dbo.log_shipping_primary_secondaries table.
The code snippet reads:

       -- there should not be any secondaries for this primary
       if exists (select * from msdb.dbo.log_shipping_primary_secondaries
                where primary_id = @primary_id)
          raiserror(32011, 16, 1, @database)
          return 1



The error raised (32011) is from master.dbo.sysmessages:


So please make sure to validate that the entries have been removed from the msdb.dbo.log_shipping_primary_secondaries table on the primary.

Job and Alert Management

Please review SQL Server Agent and alerts that might be tied to Log Shipping setup.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s