SQL Server – Log Shipping – Teardown

Background

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“.

Source

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

GUI

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”.

TransactionLogShipping

 

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.

Syntax


sp_delete_log_shipping_primary_secondary
    [ @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

 

master.dbo.sp_help_log_shipping_monitor_primary
SQL Script:

use master
go

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


Output:

TransactionLogShippingPrimaryMetadata

 

Explanation:

  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:

SELECT
	   tblLSPD.[primary_id]
         , [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]

Output:

PrimaryAndSecondaryDB

Explanation:

  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]
  go

  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
 go

 declare @database sysname

  begin tran
 
     set @database = 'HRDB'

      exec sp_delete_log_shipping_secondary_database
         @secondary_database = @database

  rollback tran

 Output:

OnSecondaryPostDeleteLogShipping

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)
       begin
          raiserror(32011, 16, 1, @database)
          return 1
       end

 

Error_DropPrimaryFirst_v2

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

sysMessages.32011

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