• Article

Purging other job-related tables

Updated: 2009-04-30

Planning Process Service processes records in the AsyncWorkItems table. This table contains the work items that have been processed by the Planning Process Service and the items that are still in the queue and will be processed by the Planning Process Service.

This table data and its related table (AsyncWorkItemStatusHistory) is not purged by any Planning process. The database administrator should monitor the size of the AsyncWorkItems table and periodically purge it. We provide a stored procedure for doing such a purge.

There are several other tables that refer to the records in the AsyncWorkItems table. The stored procedure bsp_AsyncWorkItemsPurge that is installed in the application database is only purging the records that are no longer referred to by any of those tables and the records are older than a certain date.

AsyncWorkItemID in the AsyncWorkItems table is a foreign key referred by the following tables:

  • RecurrentCycle (AutoStartTimerID),

  • CycleInstances (AutoStartTimerID, AutoEndTimerID),

  • Assignments (AutoStartTimerID, AutoEndTimerID),

  • JobInstances (WorkItemID)

The AsyncWorkItems table and its associated table data can only be deleted if none of them refer to the AsyncWorkItemID and if the ItemEndDatetime is older than a certain date-time.

The following code is the script to execute this stored procedure. This sample shows how to purge all AsyncWorkItems (and associated AsyncWorkItemStatusHistory table) records with an ItemEndDatetime column that is earlier than 2007 and only the records that are not referred by any other tables are purged.

DECLARE @RET int
EXEC @RET = bsp_AsyncWorkItemsPurge @PurgeDatetime = ‘12/31/2006’
SELECT @RET

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for PerformancePoint Planning Server.

See Also