How To: Delete Help Desk Tickets over 'X' days old

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] 
FROM [Production_Portal_HelpDesk].[dbo].[WorkOrders] 
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)

Script Details:

• 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