SQL Server Log Shipping – Bulk Remove Alerts for Deleted Databases

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.