How to remove log shipping when secondary server not available.

Recently I was working on a task to remove log shipping on one of the PRIMARY servers but it failed with below error. After checking realized that the secondary server was already decommissioned. So the log shipping components are not getting removed completely.

“SQL Server Management Studio Could not delete Log Shipping Configuration. Object reference not set to an instance of an object.”

To remove log shipping components from the PRIMARY server completely we first need to find on which databases log shipping is still enabled by using the below query:

Select primary_database FROM msdb..log_shipping_primary_databases;

Now run below 2 queries by replacing with server and database names to remove the log shipping components:

USE [master]

GO

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

GO

EXEC sp_delete_log_shipping_primary_database @database = '[Database_Name]'

Thanks VV!!