Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.

As you may know, deleting completed process instances is not really a out-of-the-box feature in K2.  You only have the option to delete running process instances.  The whole idea behind this is that for compliance reasons (e.g. SOX), process audit history is very important and should not be easily removed.

However in certain cases, it might be a good idea to remove certain processes from the K2 databases which are no longer needed.  This helps to reduce the size of the K2 Log database when the obsolete processes are not needed.  To achieve this end, I have written a simple stored procedure which allows you to remove all completed process instances from a K2 Log database.  This is based on the native K2 functionality which deletes the log entries when deleting a running process instance.

This stored procedure does the following:

  1. Removes the completed process instances based on the full name of the process.  e.g. "Finance\Purchase Request"
  2. Does a pre-check to ensure that there are no running process instances.  As I said, this is for obsolete processes, so there should not be any running instances.

The stored procedures are attached in the zip file for this post and there are two versions.  One for K2.net 2003 and one for K2 [blackpearl], the stored procedures have been tested on K2.net 2003 SP2a and K2 [blackpearl] SP1.  They should work for later versions as long as there are no changes in the lProcInstRemove stored procedure which is found in the K2 Log database.

Although due diligence and testing has been done to ensure there Stored Procedures work as advertised but in any case please do the proper DB backups before executing this is a live environment.

Note: These stored procedures are not supported by K2 directly and if any damages arise out of their use or incorrect use, myself and K2 as a company are not liable for them.  Usage of these stored procedures assumes that you have a certain level of SQL expertise so please don't cry over split milk if anything wrong occurs... :)

 I would also recommend running this when the K2 servers are offline.  This is especially true if the K2Log (K2.net 2003) or K2ServerLog (K2 [blackpearl] have a ton of records.  Executing this stored procedure on a large set of instances while the K2 server is running might cause deadlocks or timeouts to occur.

You might want to also run a DBCC SHRINKDATABASE command to reclaim execessive unused space once the obsolete process instances are removed.

I hope some of you will find this useful.  Cheers.

Posted Tue, Apr 15 2008 2:37 PM by johnny

Comments

jey wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Sun, Jul 6 2008 11:14 PM

thanks Johnny - it worked for me :) to work around an issue.

Bo Zhang wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Fri, Oct 24 2008 12:40 AM

Dear Johnny, I wonder can I use the script to delete completed process instances but the process still have some running process instances.

Of course, I mean will add some filter into the script. If there has any other influence, looking forward to get your advice, Thanks a lot in advance!

johnny wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Fri, Oct 24 2008 12:59 AM

The problem is that the script portion that deletes the K2ServerLog does not differentiate between the Completed or Running instances.

If you delete the K2ServerLog entries without checking the running instances, you might have incomplete entries in the Log database for the running instances after it completes.  I am also not too sure if this will cause issues with the reports.

If you want to do this, you probably need to modify my script to delete the completed instances and exclude the entries related to the active instance IDs.

Bo Zhang wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Fri, Oct 24 2008 1:11 AM

Sure, I think your script give me hints about which tables need to take care.

For example, I get a completed instance ID(myid) which I want to delete , then I just perform delete operation on all the tables with ProcInstID = myid. Do you think is that ok?

Thanks again!

johnny wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Fri, Oct 24 2008 1:28 AM

Yes, I think that should work.  Of course, make sure you test it out first and make a backup of your databases.

Bo Zhang wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Fri, Oct 24 2008 1:36 AM

You are great, Johnny. I really apprecaite your very helpful advice and quick reply.

Thanks Johnny!

RYF wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Thu, Mar 5 2009 8:32 AM

Great script! Gives you a good look at whats going on in the log tables.

Problem I'm having is I have a few instances that have a statusID of 5 (stopped).  Im not sure what they are but they're not active so I cant get rid of them.  The script is looking for all 3's and error's out saying get rid of all active process.

johnny wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Thu, Mar 5 2009 6:30 PM

Stopped instances are normally stopped by an administrator via the management console.  So I guess if they are stopped at client events and not doing any processing, you could use the management console to start them back up and delete them off.

minhoncan wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Tue, Apr 14 2009 11:27 PM

Hi Johnny,

Great script. As i understand, this script only delete the process instances not the process itself. Once a process is created, is there any way to delete it?

Thanks

johnny wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Tue, Apr 14 2009 11:43 PM

Currently, I did not put in the portion to delete the process definition.  I know this has been put in as an enhancement request so it should be in some future release.

wintersonata wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Mon, Oct 5 2009 7:42 PM

It would be great if we can have the script for deleting the K2 BlackPearl process definition. We can use it for the development environment where numeruous amount of obselete processes existed and require frequent scrolling down to access the current workflow in testing. Thanks in advance.

johnny wrote re: Deleting Obsolete Process Instances from a K2.net 2003 or K2 [blackpearl] database.
on Thu, Oct 29 2009 10:06 AM

I believe the history behind this was that the K2 administrator was not supposed to be able to delete off the definitions for SOX compliance.

However, if it is a test environment, one little trick is that you could delete the K2 databases and re-ran the configuration manager.  This creates a new set of DBs.  Great if you want a clean start but not so great if you need to retain some of the workflows and permission settings.

Stumbling Through wrote Stumbling Through – Clearing Test K2 Data
on Wed, Jan 27 2010 10:39 AM

In many K2 projects I've worked on, we end up having to tie existing business data into the various