Video Screencast Help

Cleaning up the Helpdesk Database

Created: 28 Jul 2008 • Updated: 28 Jul 2008 | 6 comments
Language Translations
nravenscroft's picture
+1 1 Vote
Login to vote

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 you're 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 you're 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 in 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.

License: AJSL
By clicking the download link below, you agree to the terms and conditions in the Altiris Juice Software License
Support: User-contributed tools on the Juice are not supported by Altiris Technical Support. If you have questions about a tool, please communicate directly with the author by visiting their profile page and clicking the 'contact' tab.

Comments 6 CommentsJump to latest comment

lokisson's picture

Thanks for the wide discription.
Someday we all gonna need this info.
Cheers!

0
Login to vote
drew.ohara's picture

Can you query the backup you made, of the Altiris_incidents database, for any historical info. that may be needed?

Just wondering how I would be able to get info., if needed, from the incidents that are purged.

0
Login to vote
nravenscroft's picture

It's pretty easy to just restore over top of the existing altiris_incidents database on a test box. Then you'll have access to the data. Just make sure to turn off any e-mail inboxes after you restore the database.

0
Login to vote
drew.ohara's picture

yeah, that would work. Too bad workers here like to keep incidents open for years on end. This is a good thing to have in place to keep performance up.

0
Login to vote
neil_rogers's picture

An improvement to this process would be to move the tickets to an archive server first, then delete the tickets.

while the ticket #'s might change, depending on the process to move them, the data would still be there for future reference.

0
Login to vote