SQL Server / SQL Server Management Studio could not delete the Secondary server / Secondary not available

Most of the time it is very easy to remove secondary server from your log shipping configuration. But what happens if secondary server is not available.

We had this case on one of our testing environment, we had secondary server on the cloud and after a month server lease expired. When we tried to remove that server from our log shipping configuration we end up with following errors.

Error details

As you can see from error details

SQL Server Management Studio could not delete the Secondary server ‘SQLNODE2’. (Microsoft SQL Server Management Studio)
Object reference not set to an instance of an object. (SqlManagerUI)

SQL Server can not establish remote connection, since secondary server is not available anymore .

at Microsoft.SqlServer.Management.SqlManagerUI.LogShippingSecondaryServer.LoadRemote(ServerConnection serverRemoteConn, String databaseName)

To check current log shipping configuration you can use Log Shipping System Stored Procedure.

sp_help_log_shipping_monitor this is the base behind Log Shipping Status report.

This procedure returns following

status,
is_primary,
server,
database_name,
time_since_last_backup,
last_backup_file,
backup_threshold,
is_backup_alert_enabled,
time_since_last_copy,
last_copied_file,
time_since_last_restore,
last_restored_file,
last_restored_latency,
restore_threshold,
is_restore_alert_enabled

To access Log Shipping Status report just right click on your SQL instance and choose Reports > Standard Reports > Transaction Log Shipping Status (If you used Report before it will shows under reports)

Also you can use sp_help_log_shipping_primary_secondary stored procedure and pass parameter ‘@primary_database’, and you will get secondary server name and secondary database name

So if you already know names of primary and secondary DB and secondary server solution is quite simple, to remove secondary server manually just use following script.

USE [master]
GO
EXEC sp_delete_log_shipping_primary_secondary @primary_database = 'Database_Name', @secondary_server = 'Secondary_Server_Name', @secondary_database = 'Database_Name';
GO

sp_delete_log_shipping_primary_secondary must be run from the master database on the primary server. This stored procedure removes the entry for a secondary database from log_shipping_primary_secondaries on the primary server. More info you can find on the following MS article.

If you want to remove log shipping for primary database completely you can run following script as well

USE [master]
EXEC sp_delete_log_shipping_primary_database @database = 'Database_Name'
GO

Similar Posts:

• Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)
• SQL Server / Upgrading the Secondary Server Instances (STANDBY)
• SQL Server / Initialize Secondary Database / Generate a full backup of the primary database using SSMS transaction log shipping GUI issue
• SQL Server / Backup File Size Issue
• PowerShell / SQL Server / Generate SQL Script to Restore Multiple Transaction Logs