Workpoint Database Cleanup Procedure
Article ID: 30441
Updated On:
Products
Issue/Introduction
Take extra care and ensure the application server is down completely prior to applying these instructions.
Resolution
Take extra care and ensure the application server is down completely prior to applying these instructions.
1. Backup the Workpoint Database
The Workpoint database should be backed up to some location in addition to the standard backup location. All scripts should be tested and against the extraneous backup database prior to being run on a “live” database.
Extreme care should be taken when running the attached scripts so as not to affect other possible users.
2. Execute SQL to prepare jobs for deletion.
The following SQL statement is an example that could be executed to prepare jobs for deletion.
update WP_PROCI
set LU_ID = 'Delete Job', ROW_VERSION = ROW_VERSION + 1, LU_DATE = getdate()
where INITIATION_DATE > {ts '2010-01-01 00:00:00'} and INITIATION_DATE <{ts '2010-03-31 23:59:59'}
AND (PROC_STATE_ID = 4
OR PROC_STATE_ID = 5)
This sample SQL statement sets the LU_ID as „Delete Job? for all of the completed and canceled jobs whose last used date is between January 1st, 2010 and March 31st, 2010. Workpoint itself has or needs no knowledge of this action. It is purely for the stored procedures that will be used later in this procedure.
The SQL Statement WHERE Clause
The SQL Statement may be adjusted to meet your Business Requirements. The WHERE clause can contain any filtering option to ensure the correct Jobs will be deleted such as the date range or other attributes.
For example:
“AND (PROC_STATE_ID = 4 OR PROC_STATE_ID = 5)”
This portion of the Where Clause will currently filter Jobs that are in the Canceled or Complete state. You may want to include Jobs in other states.
The current Process State Identifiers are the following:
1 Planned
2 Active
3 Error
4 Cancelled
5 Complete
6 Suspended
7 Created
3. Execute spWP_DELETE_JOBS
Execute the stored procedure spWP_DELETE_JOBS. This will delete all of the Jobs marked in step #2.
Given the size of the database involved, it may be worthwhile to gauge the execution time of the stored procedure to estimate timeframes for a run against a production database (when necessary).
Note:
This procedure does not specifically include archival of the stored data to a Workpoint archive file. However, a database backup provides all of the information that a Workpoint requires to generate those files at a later date, if so required.
Feedback
