SQL Script for cleaning up The Service Request Escalation Schedule table

Version 1

    Details

    In some situations (especially with versions prior to 2015.2) that is a possibility that the Escalation Schedule Table Frs_def_escalation_schedule will become corrupted with bad entries that are not mapped to valid related object records.  This can cause the escalation engine to stop creating Escalation Watch records for Service Requests.  This usually manifests as a missing Delivery Escalation Watch on all Service Requests.


    This article has a few scripts designed to help in the cleanup efforts.
    Database backups are highly recommended before any manual manipulation in SQL takes place.

     


    Resolution

     

    Database backups are highly recommended before any manual manipulation in SQL takes place.
    --create a backup table to reference in case anything unexpected happens
    select * into Frs_def_escalation_schedule_Bak from Frs_def_escalation_schedule

    --confirm the backup table and original table have the same row count
    select count (1) from Frs_def_escalation_schedule_Bak
    select count (1) from Frs_def_escalation_schedule


    --Each of the steps below has a select statement to check for problem records and a paired delete statement using the same logic
    --1 find and remove all ServiceReq schedules whose service req templates are not published.
    select * from Frs_def_escalation_schedule where objecttype = 'servicereq' and FieldName = 'SvcReqTmplLink_RecID' and FieldValue in (select recid from ServiceReqTemplate where status not like '%published%')
    delete from Frs_def_escalation_schedule where objecttype = 'servicereq' and FieldName = 'SvcReqTmplLink_RecID' and FieldValue in (select recid from ServiceReqTemplate where status not like '%published%')

    --2 find and remove all ServiceReq schedules who are orphaned and do not have a proper parent within the same table (RecID is populated but points to a record that does not exist)
    select * from Frs_def_escalation_schedule where objecttype = 'servicereq' and ParentEscScheduleLin_RecID not in (select recid from Frs_def_escalation_schedule)
    delete from Frs_def_escalation_schedule where objecttype = 'servicereq' and ParentEscScheduleLin_RecID not in (select recid from Frs_def_escalation_schedule)

    --3 find and remove all ServiceReq schedules who are orphaned and do not have a proper parent within the same table (value of NULL Where a RecID should be)
    --note the special exception on this line to keep the parent escalation records
    select * from Frs_def_escalation_schedule where objecttype = 'servicereq' and ParentEscScheduleLin_RecID is null and RecId not in ('1609AFCD138444CB875F035959273B0C','5B49A4ED73EE4F2E83282C34FFF076DD','B7C944C1484549479EA90631446718CD','ECBDC56EB3F34B318714D2BF46E4A491')
    delete from Frs_def_escalation_schedule where objecttype = 'servicereq' and ParentEscScheduleLin_RecID is null and RecId not in ('1609AFCD138444CB875F035959273B0C','5B49A4ED73EE4F2E83282C34FFF076DD','B7C944C1484549479EA90631446718CD','ECBDC56EB3F34B318714D2BF46E4A491')

    --4 find and remove all ServiceReq schedules who are orphaned from their Service Request Template Records
    select * from Frs_def_escalation_schedule where objecttype = 'servicereq' and FieldName = 'SvcReqTmplLink_RecID' and FieldValue not in (select recid from ServiceReqTemplate)
    delete from Frs_def_escalation_schedule where objecttype = 'servicereq' and FieldName = 'SvcReqTmplLink_RecID' and FieldValue not in (select recid from ServiceReqTemplate)


    --Confirm the root schedules are still intact - without these nothing works at all!  We want to confirm these are still present and intact...  This should return 5 records.
    select * from Frs_def_escalation_schedule where recid in ('B6F774D7F337460EA1F0154F6E451BC7','1609AFCD138444CB875F035959273B0C','5B49A4ED73EE4F2E83282C34FFF076DD','B7C944C1484549479EA90631446718CD','ECBDC56EB3F34B318714D2BF46E4A491')