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



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.


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




Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *