Workflow 1.0 and 1.1 - SQL Server Maintenance

Document ID : KB000055484
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

This document guides the maintenance of the Workflow schema in production when that schema is hosted in SQL Server. If you fail to follow these guidelines, performance and capacity problems are likely to result.

Example:

Document Version: 2.1
Workflow Build: 1.0. 019.040 (I20070503_E20070504_g3) or later; all builds of 1.1
Star Solutions: 220 (QI83103), 195 (QI81062), 218 (QI86805) (WEK 1.0)

When CA Workflow stores its schema in a SQL Server database, its tables can grow very large. This is true whether the SQL Server hosts Workflow in an MDB or by itself. (The latter is not supported by all embedding products.)

Database growth can happen quite rapidly and particularly affects the instances table, which could occupy over half of the total size of the database. The SQL Server utility sp_spaceused executed on the instances table shows most of the space occupied by the table to be marked as unused space. This occurs due to normal operations, which store process instances that include a copy of the process definition at the time of execution. For a link to the complete list of Workflow tables, please refer to Appendix A.

To reduce the amount of space taken by the Workflow tables, follow this procedure:

  1. Purge old process instances, depending on the amount of workflows that are executed daily, and whether you want to retain the most recent instances or not.

    This can be done either manually through the Workflow Design Environment (IDE), or programmatically by calling methods in the PMService interface

    A couple of examples:

    • Monthly removal of all completed instances (IDE)
      Log into the Workflow IDE, Process Manager, Process Instances. Select Commands, Purge Instances, uncheck the "All Definitions" box and select the definition whose instances you want to purge. Click on the "From" calendar button and go back one or two months, so that you ensure that no instances will be left behind:

      Figure 1

      Click OK, accept the warning and Workflow will delete all the completed instances for SharingSessions_1. Note that you may check All Definitions to purge all process definitions in a single operation.

      Note that you will likely receive the following error message:

      Figure 2

      This is normal, as some instances will likely be in running or suspended status.
      Please note that the IDE might have problems when attempting to purge a high number of instances: should that happen, try to purge instances more frequently, and deselect "All Definitions" in order to work on one definition at a time.

    • Programmatic use of web services

      These web service calls are available in the PMService interface:

      • purgeAsynchronouslyForCompletedWithStats

      • purgeInstances

      • purgeInstancesAsynchronously

      • purgeInstancesSynchronously

      • purgeSynchronouslyForCompletedWithStats

  2. See APAR QI74335 for details.

  3. In SQL Server, use the Transact-SQL command:
    sp_spaceused <table name>, @updateusage = 'true'
    to ensure that the correct space utilization report gets generated.

    Please see the Appendix B at the end of this document for additional information on the sp_spaceused procedure. Further information on sp_spaceused can be found at: http://msdn2.microsoft.com/en-us/library/ms188776.aspx.

  4. Then run:
    DBCC SHOWCONTIG (<table name>)
    to determine if the table is heavily fragmented. If there is index fragmentation, the indexes can be dropped and recreated, reorganized, or rebuilt. Details on the usage of the DBCC SHOWCONTIG utility and reducing fragmentation can be found at: http://msdn2.microsoft.com/en-us/library/ms175008.aspx

  5. Use:
    DBCC SHRINKDATABASE (<dbname>, <percent desired free space>)
    to shrink the size of the data and log files. Details on the usage of the DBCC SHRINKDATABASE utility can be found at:
    http://msdn2.microsoft.com/en-us/library/ms190488.aspx

  6. Use:
    DBCC SHRINKFILE
    to reclaim data and log file space. Details on the usage of this utility can be found at:
    http://msdn2.microsoft.com/en-us/library/ms189493.aspx

  7. The steps above will free up the contiguous blocks of free space at the end of the data files. To reclaim non-contiguous free space on the extents/data pages the data in the base table will need to be reordered by exporting out the data, clearing the table of all rows and importing the data back in to the table. To achieve this use the BCP bulk copy command line utility.
    Details on the usage of this utility can be found at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

Appendix A - Workflow Database Tables

The full list of tables used by CA Workflow is found in APAR QI81062.

To find the columns in these tables and their data types, consult the particular schema file in the distribution image that is appropriate for the database that hosts the schema.

NOTE: All data definitions in the Workflow schema are subject to change in subsequent releases.

Appendix B - Incorrect info reported by sp_spaceused procedure

On SQL Server 2000 SP4, the stored procedure sp_spaceused reports that the CA Workflow database schema allocates huge amounts of disk space in SQL Server, yet nearly all of it is marked as unused. Workflow does not actually allocate space at all; it merely inserts and updates rows.

However, many of these rows contain BLOB data, and in SQL Server 2000 SP4, sp_spaceused ignores BLOB data in its report of how much space a table uses.

Other SQL Server versions, like 2000 SP3 or 2005, do not appear to have this behavior.