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?
Release: 14.1 and higher
Component: CA Service Management
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.
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.
If you use surveys, you may have lots of old surveys stored in the database. These are stored in three different mdb tables:
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.
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