The Deleted Data Cleanup job is unable to update the status to Deleted, Cleanup Pending

Products

Issue

The Deleted Data Cleanup job shows errors similar to the following:

Delete old data 24/02/10 3:03:54 PM Task Unable to update the status to “Deleted, Cleaup Pending” for profile 145
Delete old data 24/02/10 3:03:54 PM Task Progress Updating status on profile 145 – Profile1
Delete old data 24/02/10 3:03:54 PM Task Unable to update the status to “Deleted, Cleaup Pending” for profile 143
Delete old data 24/02/10 3:03:54 PM Task Progress Updating status on profile 143 – Profile2
Delete old data 24/02/10 3:03:54 PM Task Unable to update the status to “Deleted, Cleaup Pending” for profile 137

Cause

This is likely due to an issue with an invalid constraint in the database. This occurs when one table entry that references another table entry is missing, something which an MS SQL server does not to occur. When the Deleted Data Cleanup job runs, the query to change the state is unable to do so because it will create an invalid constraint in another table, by which the request will be denied by the database server.

Resolution

Clear out the Monitor tables that are being referenced so the invalid constraint will no longer be an issue and the job will be able to update the table accordingly.

1.For installations using MS SQL Server 2008, download and install Microsoft SQL Server Management Studio. For more information, refer to the article titled “How do I install the stand-alone version of SQL Server 2008 Management Studio?“.

For installations using MS SQL Server 2005, download and install Microsoft SQL Server Management Studio Express from the following URL:

http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en


2. After Microsoft SQL Server Management Studio Express has been installed, launch the application to connect to the system database. The “Connect to Server” dialog box displays.

• In the “Server name:” field, enter the name of the Webtrends server on which the system database is installed.
• In the “Authentication:” field select “Windows Authentication” if logged into the Windows machine using the Webtrends service account or an account with rights to make changes to the system database. Otherwise, select “SQL Server Authentication” from the drop-down menu and enter the credentials for a SQL Server account which has the needed rights. Click “Connect” to connect to the database.

It is recommended a backup of the database is made before proceeding. For more information, see the article titled “Backing Up and Restoring the System Database using MS SQL Management Studio“.

3. Select “New Query” and paste the following queries into the query pane and then click “Execute.”

DELETE FROM wt_sched.dbo.WT_MonitorResultAction;
DELETE FROM wt_sched.dbo.WT_MonitorAlertResolveHistory;
DELETE FROM wt_sched.dbo.WT_MonitorResult;

The Monitor tables have now been cleared and the Deleted Data Cleanup job should now be able to run without creating invalid constraints.

Further information:
To run these queries from a command prompt, refer the article titled “How to run queries from a command prompt“.