How to clean up a WF4 persistence database

Introduction

The SqlWorkflowInstanceStore class in WF4 uses a Sql Server database for persistence. See this post for more details on how you can create the schema and the logic for the persistence database.

The database stores the serialized form of workflow instances, along with lock and key information.

Database clean-up

You may need to restart from a clean database from time to time. This can be a consequence of some testing, that caused the accumulation of a number of persisted instances that you do not want to, or can’t, continue to execute. In these cases, you may decide to remove the database altogether, create a new one and re-create its schema and logic.

However, there may be scenarios where deleting and re-creating the database is not convenient, for one of these reasons:

• You changed the permissions in the database. The typical case is when the client process is the IIS/WAS worker process W3WP.EXE, and you added a user for its identity (NETWORK SERVICE, for instance) and appropriate role membership for it.
• You need to act quickly, possibly because this is a production environment that ended up in an unexpected state and you want to quickly restore a working environment.
• You are sharing the persistence DB with “something else”. For instance, the AppFabric Monitoring and AppFabric Persistence schemas (a superset of the WF4 Persistence schema) can coexist in the same database.

In these cases, you may prefer to simply clean up the existing database.

A few caveats apply:

• Cleaning up the persistence database may cause loss of data: if there are workflow instances currently persisted in the database, you won’t be able to reload them in memory at a later time, after cleaning up the persistence database.
• The clean up of the database should take place when all its users (all the applications that are using this database as the persistence store for workflows) are in a stopped state. Running processes that use workflows may make use of existing entries in the LockOwnersTable and would raise an exception if those entries are removed.
• The schema of the persistence database may change in future releases of WF. It is not guaranteed that this procedure will work with future releases of WF. The current release as of this writing is WF 4.5.

Clean up consists in truncating the contents of nearly all the persistence tables in the database, This is a stored procedure that you can use:

 CREATE PROCEDURE [dbo].[DeleteAll] 
AS
 BEGIN
     SET NOCOUNT ON;
     SET TRANSACTION ISOLATION LEVEL READ COMMITTED
     SET XACT_ABORT ON;    

    BEGIN TRANSACTION

    TRUNCATE TABLE [System.Activities.DurableInstancing].[IdentityOwnerTable];
    TRUNCATE TABLE [System.Activities.DurableInstancing].[InstanceMetadataChangesTable];
    TRUNCATE TABLE [System.Activities.DurableInstancing].[InstancePromotedPropertiesTable];
    TRUNCATE TABLE [System.Activities.DurableInstancing].[KeysTable];
    TRUNCATE TABLE [System.Activities.DurableInstancing].[LockOwnersTable];
    TRUNCATE TABLE [System.Activities.DurableInstancing].[RunnableInstancesTable];
    TRUNCATE TABLE [System.Activities.DurableInstancing].[ServiceDeploymentsTable];
    TRUNCATE TABLE [System.Activities.DurableInstancing].[InstancesTable];

    COMMIT TRANSACTION
 END

Note: the table IdentityOwnerTable is part of the Workflow Persistence Schema since WF4.5. Therefore, if you are using WF4.0, the first TRUNCATE statement in the procedure above should be removed.

Comments

• Anonymous
  June 14, 2014
  Thanks. One note: you don't need to use transaction, because you can't rollback truncate - it stores nothing to transaction log
• Anonymous
  July 22, 2014
  Thank you Dmitriy for your comment.I am not at all a database expert, I did some quick research and it is my understanding that TRUNCATE writes to the transaction log differently than DELETE, but the operation can be rolled back.This article: sqlperformance.com/.../drop-truncate-log-myth explains how it works.