Log Shipping: Bulk Remove Alerts of already deleted primary and secondary databases

Question

I'm facing a rather strange issue with Transaction Log Shipping reports in SSMS which is showing multiple red alerts of databases that have been created and deleted while performing multiple Log Shipping scenario between two servers.
I've used the following post:Remove the log shipping configuration on the secondary server, when the primary is no longer available to help clear those alerts but nothing came out of this with success. Sometimes i'm getting the following error:

Msg 32011, Level 16, State 1, Procedure sp_delete_log_shipping_primary_database, Line 51 Primary Database MyFirstDB has active log shipping secondary database(s). Drop the secondary database(s) first

Which is not true as the database is already deleted on both primary and secondary server as well as three log shipping jobs.
I have more than forty orphaned databases in this state. this is saturating SQL server log with the below error for all aforementioned databases:

Date 19/08/2017 08:30:00 Log SQL Server (Archive #3 - 19/08/2017 08:31:00)

Source spid53

Message
The log shipping secondary database SQLTEST.MyFirstDB has restore threshold of 30 minutes and is out of sync. No restore was performed for 449177 minutes. Restored latency is 2 minutes. Check agent

Can you help please, clear these entries from wherever system database/tables holds these records.?
I really appreciate the help!

Answer 1

Information about databases involved in log shipping are contained in several system tables in the msdb database. Looking at these tables will help identify whether you've properly reconfigured log shipping.

This first query shows databases that are configured as primaries, where the database no longer exists on the SQL Server instance:

SELECT lspd.primary_database
    , lspd.last_backup_date
FROM msdb.dbo.log_shipping_primary_databases lspd
WHERE NOT EXISTS (
    SELECT 1
    FROM master.sys.databases d
    WHERE d.name = lspd.primary_database 
    );

This query will show secondaries for the databases identified above:

SELECT lsps.secondary_server
    , lsps.secondary_database
FROM msdb.dbo.log_shipping_primary_secondaries lsps
    INNER JOIN (
        SELECT lspd.primary_id
        FROM msdb.dbo.log_shipping_primary_databases lspd
        WHERE NOT EXISTS (
            SELECT 1
            FROM master.sys.databases d
            WHERE d.name = lspd.primary_database 
            )
        ) p ON p.primary_id = lsps.primary_id;

Use sys.sp_delete_log_shipping_primary_secondary to remove primary databases from the log shipping configuration.

Use sys.sp_delete_log_shipping_secondary_database to remove affected secondaries, if desired, which should stop the alerts you're seeing.

The Microsoft Docs page on monitoring log shipping shows several useful details, including the following tables that are used by the monitor process:

• log_shipping_monitor_alert - Stores alert job ID.

• log_shipping_monitor_error_detail - Stores error details for log shipping jobs. You can query this table see the errors for an agent session. Optionally, you can sort the errors by the date and time at which each was logged. Each error is logged as a sequence of exceptions, and multiple errors (sequences) can per agent session.

• log_shipping_monitor_history_detail - Contains history details for log shipping agents. You can query this table to see the history detail for an agent session.

• log_shipping_monitor_primary - Stores one monitor record for the primary database in each log shipping configuration, including information about the last backup file and last restored file that is useful for monitoring.

• log_shipping_monitor_secondary - Stores one monitor record for each secondary database, including information about the last backup file and last restored file that is useful for monitoring.

Querying the contents of those tables should help you narrow down the source of the alerts. The page I mentioned above also contains details about several monitoring stored procedures you can use to query the tables.

The "Transaction Log Shipping Status" report calls the sp_help_log_shipping_monitor stored procedure to get its details - looking at the T-SQL definition of that stored proc may help you resolve where the issue is.