This script will delete Help Desk tickets older than a specified date.
1. Take a backup of the Help Desk database.
2. Alter the following script to match your Help Desk name, as well as the number of days old.
DELETE From Production_Portal_HelpDesk.dbo.WorkOrders where workorderID in (SELECT workorders.[WorkOrderID]
INNER JOIN (Select WorkOrderID, max(TimeStamp) as closedate from [Production_Portal_HelpDesk].[dbo].[ChangeLog] group by workorderID) cl on cl.WorkOrderID = WorkOrders.WorkOrderID
where status = 0 and helpdeskID = # and datediff(day,cl.[CloseDate],getdate()) > #days)
- The Help Desk ID # is for the specific help desk. This can be changed to (helpdeskID = # or helpdeskID = #) if you need to delete from multiple help desks. You don’t need this part if all help desks are going to be affected.
- The number of days old is the #days in the script.
- Getting the close date is a bit tricky due to there not being a close date on the ticket itself, but it’s calculated as the last time the ticket was updated.
- It is advised that the script be run with a Select statement first to ensure only the correct tickets are being deleted.
- This can be set up in a recurring SQL Job to run daily or weekly as well