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”.
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.
sp_delete_log_shipping_primary_secondary [ @primary_database = ] 'primary_database', [ @secondary_server = ] 'secondary_server', [ @secondary_database = ] 'secondary_database'
Sample – Prep
Let us be careful and know exactly what values to pass in…
We know that we need to pass along the following parameters
- Primary Database
- Secondary Server
- Secondary Database
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
- We have the primary server and database
- The last time a backup occurred along with the name of the ensuing backup file
- But, nothing about the secondary server nor the destination database name
MSDB – log_shipping_primary_databases & log_shipping_primary_secondaries
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]
- There we have it
- We have our primary Server and DB
- And, our secondary Server and DB, as well
Sample – Actual
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
use master go declare @database sysname begin tran set @database = 'HRDB' exec sp_delete_log_shipping_secondary_database @secondary_database = @database rollback tran
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
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.