LogShipping:Removing log shipping history after dr drill

Removing Job history after DR-DRILL:

1After removing log shipping of the database and its respective job but the job still runs, it showing latency job on another server

Command to monitor LogShipping:

select secondary_database as DBNAME

last_restored_date as [LAST RESTORE DATE]

DATEDIFF(minute

case

when

DATEDIFF(minute

, last_restored_date

, last_restored_date

,

,

, primary_server as [PRIMARY SERVER]

GETDATE()) AS LATENCY

,

,

GETDATE()) < 60

,then 'SYNCHRONIZED'

else 'UNSYNCHRONIZED'

end

as [LOGSHIPPING STATUS]

from msdb

go

sp_help_log_shipping_monitor

select secondary_database as DBNAME

case

when

DATEDIFF(minute

then 'SYNCHRONIZED'

else 'UNSYNCHRONIZED'

end

as [LOGSHIPPING STATUS]

from msdb

go

The following disabled job are useless:

Delete job manually:

-Do you want to delete the selected jobs?

Yes

Before doing any changes first back up log shipping tables:

To make backup of table

--select * into msdb..log_shipping_monitor_primary_cp

--from msdb..log_shipping_monitor_primary

To get primary job id:

select * from msdb..log_shipping_monitor_primary

To remove primary job history:

--DELETE FROM msdb..log_shipping_monitor_primary WHERE primary_id ='3D8CA04C-4675-499D-

97E2-F5F31793644B'

And verify backup table rows with original table rows:

Then check and verify last backup date time threshold on both servers:

The left server on above is having huge threshold and its backup job is removed already. To remove it we get primary id from following query:

Update table by removing the primary id of above job:

On secondary server check last copy & restore times also to verify that copy and restore job secondary id:

Backup table secondary:

--select * into msdb..log_shipping_monitor_secondary_cp

--FROM msdb..log_shipping_monitor_secondary

Verify backup:

SELECT *

FROM msdb..log_shipping_monitor_secondary_cp

To Get Secondary job id:

SELECT *

FROM msdb..log_shipping_monitor_secondary

To delete job history from secondary server:

--DELETE FROM msdb..log_shipping_monitor_secondary WHERE secondary_id ='23AD6087-4A91-4A5E-BE3C-9447145BB2E7'

Delete the job from secondary server by verifying its id: