1

I dropped an old unused database. Then I found it was set up to be the source database of a logshipping setups. And there are a lot failing jobs in our job running status report now. How to remove the logshipping settings after the database is already removed?

1
  • How exactly are the status jobs failing? Are you sure they aren't just hard-coded to check the status of the logs for individual databases? Mar 2, 2015 at 15:42

1 Answer 1

0

In order to fix alert issues use database: msdb and

First of all find out details on Primary Server by executing following..

PRIMARY SEVERE

SELECT * FROM dbo.log_shipping_monitor_primary
where primary_database like 'CHANGE_DB_NAME%'


SELECT * FROM dbo.log_shipping_primary_databases
where primary_database like 'DB_NAME_HERE%'

SELECT * FROM dbo.log_shipping_primary_secondaries
where secondary_database like 'DB_NAME_HERE%'

Once verifies DB names execute

delete FROM dbo.log_shipping_monitor_primary
where primary_database like 'DB_NAME_HERE%'


delete FROM dbo.log_shipping_primary_databases
where primary_database like 'DB_NAME_HERE%'

delete FROM dbo.log_shipping_primary_secondaries
where secondary_database like 'DB_NAME_HERE%'

Follow same steps on SECONDARY SERVER

SECONDARY

SELECT * FROM dbo.log_shipping_monitor_secondary
where secondary_database like 'DB_NAME_HERE%'


SELECT * FROM dbo.log_shipping_secondary_databases
where secondary_database like 'DB_NAME_HERE%'

SELECT * FROM [dbo].[log_shipping_monitor_primary]
where primary_database like 'DB_NAME_HERE%'

and once confirmed delete details from table by executing following....

delete FROM dbo.log_shipping_monitor_secondary
where secondary_database like 'DB_NAME_HERE%'


delete FROM dbo.log_shipping_secondary_databases
where secondary_database like 'DB_NAME_HERE%'

delete FROM [dbo].[log_shipping_monitor_primary]
where primary_database like 'DB_NAME_HERE%'

Hope this will help you to clear your unnecessary notifications from msdb database. It happens when you do not clear log-shipping clearly or in your case database dropped.

4
  • Thanks. It seems a couple of jobs were created in the SQL Agent. Should these jobs be removed manually? Or anything else need to be done for removing these jobs?
    – u23432534
    Mar 3, 2015 at 4:28
  • You can execute above queries and delete entries from tables in madb and you can manually remove SAL Agent Jobs by deleting them.
    – Im88
    Mar 3, 2015 at 14:39
  • There are two jobs: LSAlert_ServerName and LSBackup_DBName. Is it safe manually remove both if there is no logshipping set on the server? Or LSAlert_Server should be kept if there are other logshipping on the server?
    – u23432534
    Mar 3, 2015 at 16:00
  • You can remove LSAlert_ServerName and LSBackup_DBName job manually as there is no DB left on instance for LS. LSAlert_ServerName will be recreate once you have LS setup again first time. LSAlert_ServerName is one job per server.
    – Im88
    Mar 3, 2015 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.