How to remove Log Shipping plans in LiteSpeed 4.8.x version and below?
A GUI tool is not available to remove LiteSpeed Log Shipping Plans for version 4.8.x and below.
RESOLUTION 1
1. Login to the SQL instance and set the database context to LiteSpeedLocal. Query the LSPlans Table to find out the PlanID:
Example:
PlanID PlanName PDescription Date
D0DD84A8-2B60-4DE4-87FE-4ACF000AD6A8 Test Test Plan for LogShippng NULL
(The PlanID is D0DD84A8-2B60-4DE4-87FE-4ACF000AD6A8 in this example)
2. Run the SLS_Remove_Planstored procedureagainst the PlanID:
exec SLS_Remove_Plan @PlanID = D0DD84A8-2B60-4DE4-87FE-4ACF000AD6A8.
RESOLUTION 2
If for some reason, SLS_Remove_Plan fails, perform the steps below against the publisher or subscriber to manually delete the logshipping records from all the underlying LiteSpeed tables.
1. Disable the log shipping jobs from the SQL Server agent
2. Get the plan ID from LSPlans:
select * from LSplans
Example:
PlanID PlanName PDescription Date
D0DD84A8-2B60-4DE4-87FE-4ACF000AD6A8 Test Test Plan for LogShippng NULL
3. Run the attached sp_LS-PlanID-SELDEL-Utility_version_484.sql under the LiteSpeedLocal context to create a temporary stored procedure toperform a cleanup.
NOTE: If creating a procedure is not allowed, then proceed to step 8.
4.Select the log shipping tables to confirm the correct PlanID to be deleted:
execute [dbo].[sp_LS-PlanID-SELDEL-Utility_version_484] Select,D0DD84A8-2B60-4DE4-87FE-4ACF000AD6A8
5. Once confirmed, delete the log shipping records for the particular PlanID:
execute [dbo].[sp_LS-PlanID-SELDEL-Utility_version_484] Delete,D0DD84A8-2B60-4DE4-87FE-4ACF000AD6A8
6. Confirm that there are no more records for the particular PlanID:
execute [dbo].[sp_LS-PlanID-SELDEL-Utility_version_484] Select,D0DD84A8-2B60-4DE4-87FE-4ACF000AD6A8
7. Drop procedure [dbo].[sp_LS-PlanID-SELDEL-Utility_version_484]
8. If sp_LS-PlanID-SELDEL-Utility_version_484 was not used, then delete the log shipping records based on the PlanID from the following tables:
LSJobs
LSRestoreLog
LSCopyLog
LSBackupLog
LSBaseOptions
LSDatabasePlans
LSJobs
LSServerPlans
LSUNC
LSPlans
LStrackinglog
9.Clean the remaining LS tables:
select * from LSdatabase
delete from LSdatabase
select * from LSserver
--If the log shipping plan is the only plan on this particular server, then delete the rows. If there are other plans, then do not delete from LSserver)
delete from LSserver
10.Manually delete the log shipping jobs from the SQL Server agent
