Over time the helpdesk database becomes very laden with sludge. In our case in over 3 years of heavy use the helpdesk has been in place we have accumulated over 1.5 million incidents, 8500 obsolete contacts and 150 worker queues. With every passing month my users noticed the system getting slower and slower.
What to do with all this filth you ask, well purge it of course. First off we needed to come up with a policy and then figure out how to implement the policy. Today we'll work with the incidents database.
After sitting down with the users of the system we were able to determine that we only needed 6 months of tickets.
Once the policy was determined the fun part began. Since there is no built in method inside of the Altiris Helpdesk system to purge incidents, we needed to figure out how to do this from SQL.
A word of warning before we get started: Make sure you have a good backup of your Altiris_Incidents database and preferably test this process thoroughly on a test box first. Also everything here is provided as is, these processes work in my environment, but may need some tweaking to work in yours.
With that out of the way, on to the fun stuff.
All the incidents are stored in the workitem table. The records in the worker table only have one dependency that we have to worry about when purging the records. That column is called link_parent_number and is used for linking child and parent incidents and the default value should be 0. I found it easier to identify these records after the purge, so well start with the purge then clean up the left over records.
First we'll start with creating a query to select all the records over 6 months old. Open SQL management studio select the Altiris_Incidents database and create a new query.
Select count(*)
FROM workitem
WHERE modified_on < DATEADD(mm,6*-1,GETDATE());
This will give us the number of records in the workitem table that are older than 6 months.
Now remember that each workitem may have multiple records in the workitem table. If you want to see the total number of actual incidents change the query to:
Select count(distinct number)
FROM workitem
WHERE modified_on < DATEADD(mm,6*-1,GETDATE());
You can modify this query as needed until you get the records you want to purge. Once we have our counts it's time to prepare to delete the old incidents. At this point, you'll want to once again verify you have a good backup before preceding.
Now we'll need to modify your select query into a delete query. This is accomplished by changing the select x,x,x to delete. I always like to use a BEGIN TRANSACTION , COMMIT TRANSACTION/ROLLBACK TRANSACTION to the start and end of my queries whenever I'm deleting our modifying tables as if something goes wrong I can quickly rollback without restoring the database.
BEGIN TRANSACTION
Delete FROM workitem
WHERE modified_on < DATEADD(mm,6*-1,GETDATE());
-- COMMIT TRANSACTION
-- ROLLBACK TRANSACTION
Once you run this you'll want to verify that the number of rows affected matches the number of rows selected in your original query. You can also go into the helpdesk and verify everything is working correctly and that you can create new tickets and view existing ones.
If your happy with the results go ahead and uncomment the COMMIT TRANSACTION and execute it. Otherwise uncomment the ROLLBACK TRANSACTION and execute it, and everything will be back like it never happened.
After the old incidents are purged we still need to clean up any lingering parent/child linking issues. If you don't link tickets then you can skip this step, but I would recommend you do it anyway as it won't hurt.
As before we will start with a query, but this time will change it to an update instead of a delete.
We need to find all the incidents that reference incidents that are no longer in the database .
Select count( *) from workitem
where link_parent_number != 0
and link_parent_number not in (select distinct number from workitem)
This query will search the workitem table, and return any record that doesn't have a 0 (the default) in the link_parent_number column, and the incident number referenced in the link_parent_number is not in the workitem table.
Now we modify the this query into an update
BEGIN TRANSACTION
update workitem
set link_parent_number = 0
where link_parent_number != 0
and link_parent_number not in (select distinct number from workitem)
-- COMMIT TRANSACTION
-- ROLLBACK TRANSACTION
Once you run the update you will need to remember to un comment and execute either the commit or rollback transaction.
There you have it you have successfully purged your helpdesk database of incidents over 6 months old.
There are a couple caveats with purging the workitem table like this. If you have a lot of activity going on in a live environment and your deleting many records at once you may run into performance or locking issues while your running the deletes. Also copying and pasting SQL code or rewriting something I did 6 months ago but now forgot is not something I look forward to. To solve these problems we'll now put all our code together into a stored procedure. I have attached the completed stored procedure so you can follow along. Most of this code will already be familiar to you as we covered it above, but I'll go ahead and go through each section so you can understand what it's doing and customize it to your environment.
We start with the general CREATE /ALTER Procedure statement. If you already have the stored procedure in your database the you can change the CREATE below to an ALTER and your update the existing procedure.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PurgeIncidentTable] AS
To make the Stored Procedure more versatile, in this section of the code we declare and set variables. These variables allow your to define things that change over time in one place and not have to go through all the code and manually updating values whenever requirements change.
SET NOCOUNT ON
DECLARE @rows int
DECLARE @batchsize int
DECLARE @monthstokeep INT
DECLARE @totalrows INT
DECLARE @delay VARCHAR(10)
SET @batchsize = 1000
SET @monthstokeep = 6
SET @delay = '00:00:02'
SET @totalrows = 0
SET @rows = 1
In the next section we are translating our original delete query into a loop to resolve the locking issue outlined above that could occur when purging 10000s of records is one swoop. Instead we take all the rows to purge, and delete them in batch sizes of 1000 (SET @batchsize = 1000) we then add the number of rows we deleted in the loop itineration to the @totalrows. Then we wait the amount of time in @delay ( in our case 2 seconds), then repeat through the loop again until all the rows are purged. Once purged we then print to the screen that the table was purged and how many rows were purged.
-- Delete obsolete Rows in the workitem table
WHILE (@rows > 0)
BEGIN
DELETE TOP (@batchsize)
FROM workitem WITH (PAGLOCK)
WHERE modified_on < DATEADD(mm,@monthstokeep*-1,GETDATE());
SELECT @rows = @@ROWCOUNT;
SET @totalrows = @totalrows + @rows;
WAITFOR DELAY @delay;
END
PRINT 'workitem table Purged..'
PRINT CAST(@totalrows AS VARCHAR(20)) + ' records purged'
In the next and final block we are pretty much just copying the Link parent number cleanup update query we created earlier. We also add a couple of PRINT lines to the statement to echo out how many rows we update. With that the stored procedure is complete.
-- Fix any Obsolete parent links
update workitem
set link_parent_number = 0
where link_parent_number != 0
and link_parent_number not in (select distinct number from workitem)
SELECT @rows = @@ROWCOUNT;
PRINT 'workitem obsolete linked parents fixed..'
PRINT CAST(@rows AS VARCHAR(20)) + ' records fixed'
To add the Stored procedure to your system open SQL management studio, and in a new query window, execute the code. This will create the Store Procedure in your database.
Now all you have to do to purge your database of records older than 6 months is run
EXEC PurgeIncidentTable
You can also schedule this procedure to run via the SQL job scheduler on a daily/weekly or whatever basis.
In conclusion, I have been running the PrugeIncidentTable procedure in production for over 6 months without issue. Our Helpdesk's live online incident count has reduced from over 1.5 million to around a running average of 330,000 active incidents, and the performance on the helpdesk is now stable and predictable.
PurgeIncidentTable.zip (700.00 bytes)