Reducing the amount of data in the Service Desk database

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

Service Desk has an archive and purge mechanism built in, which can be used to reduce the amount of data in the mdb database. Is there anything else which can be done?

Environment

Release:  14.1 and higher
Component: CA Service Management

Resolution

The Service Desk archive and purge run can be used to remove old Requests (including Incidents and Problems), Change Orders and Issues. It also removes old records from the Notification Log History. These database tables normally hold the most information, so reducing the information in this table can be useful, however, there are other database tables from which information can safely be removed. In these examples, I'll remove data from before 1st January 2006. You can obviously change this date to suit your circumstances.

Audit Log

If auditing is enabled, it can quickly create a lot of information in the audit_log database table. Once this information is old enough to not be required on-line, it can be "archived" into a data file, and this information removed from the database. To do this, use pdm_extract:

pdm_extract -f "select * from Audit_Log where change_date < DATE '2006-01-01' " > audlog_pre_2006.dat

This will take all of the entries in the audit_log database table where the date of the audit event was earlier than the 1st January 2006. You can then use this data file to remove this data, using the pdm_load command.

pdm_load -r -f audlog_pre_2006.dat

This command will remove the information from the Service Desk mdb. The "audlog_pre_2006.dat" file can be kept as an "offline backup" of this data, and pdm_load could be used at some point in the future, should this information be required again.

Service Type Events

If your Events have "Save History" set in the "On Done Event Flag", this can lead to a lot of old information being stored in the mdb. The same approach can be used as with the audit_log table:

pdm_extract -f "select * from Attached_Events where status_flag=1 and start_time
< DATE '2006-01-01' " > atev__pre_2006.dat
pdm_load -r -f atev_pre_2006.dat

This will select all of the service type event records which are completed (the status_flag=1 condition) and have a start date of before 1st January 2006, and save them into the file. The saved file is then used to remove these records from the mdb.

Surveys

If you use surveys, you may have lots of old surveys stored in the database. These are stored in three different mdb tables:

• survey - This contains the surveys whcih have been filled in.
• survey_questions - This contains the survey questions which were attached to the above surveys.
• survey_answers - This contains the answers which were submitted for the above questions.

You can do the same as for the audit_log or attached_events to remove old data:

pdm_extract -f "select * from Survey where last_mod_dt < DATE '2006-01-01' " >
survey_pre_2006.dat
pdm_load -r -f survey_pre_2006.dat
pdm_extract -f "select * from Survey_Question where last_mod_dt < DATE '2006-01-01' " >
survey_question_pre_2006.dat
pdm_load -r -f survey_question_pre_2006.dat
pdm_extract -f "select * from Survey_Answer where last_mod_dt < DATE '2006-01-01' " >
survey_answer_pre_2006.dat
pdm_load -r -f survey_answer_pre_2006.dat

These commands will again save all of the information before 1st January 2006 and then use the saved data to remove data from the mdb.

Additional Information

Before attempting to remove data from the mdb, always ensure that you have a backup of the data, should anything go wrong.

Additionally, please test any changes in a non-prod instance before attempting in production.  

Please do not use this method on any other tables besides the tables mentioned in this KB