SharePoint Blogs / SharePoint University
SharePoint Blogs and SharePoint University - all in one place!
Dave Wollerman's SharePoint Blog » Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
Need SharePoint Training? Attend a SharePoint Bootcamp!

Please delete cookies related to sharepointblogs.com and sharepointu.com to resolve login issues!

Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!

There is a problem with the workflow in MOSS. For some reason Microsoft decided that they need to purge the workflow associations after they are 60 days old. This means that when you looks at the workflow history for an item you will not see workflow history older than 60 days. The information about the workflow is still in the tasks and workflow history lists, but the association between the workflow and the item has been purged from the database. I know of some people who have already contacted Microsoft about this problem and created support cases for it. The answer they keep getting is that it is by design for performance reasons. What about auditing reasons Microsoft, don't you think this will be a huge issue for enterprise clients?

 

My question is how many clients out there are using this for important business processes thinking their audits will be covered? If you are a Microsoft support customer and are using workflow for important business process that will be audited. PLEASE CALL THEM AND COMPLAIN!!! The only way it seems that Microsoft will move on this is if they get a large enough response from the community. Obviously a couple large enterprise companies (50,000+ employees) aren't enough to concern them.

 

Below are some details of the issue and some correspondence with Microsoft support...

  • Document losing their “approval workflow details” after 60 days.
     
  • Workflow details still on site, but no longer associated to respective documents.
     
  • Issue identified by Microsoft and not listed as a bug as originally told – this expiration of 60 days is something that was done purposely - “by-design”.
     
  • MS provided code that will allow us to extend the document/workflow history relationship past 60 days. (code needs to run everyday to change new associations)
     
  • Testing has not been done by Microsoft for the provided solution.

Here is a list of findings and concerns on this situation:

  • FINDING - A table that contains associations between workflow details and documents contain this expiration date of 60 days.
     
  • FINDING - This field can be changed, on a site basis, to a maximum of 9999 days – 27 years or so… at least according to Microsoft (untested as of yet).
     
  • PROBLEM – If code works successfully, it will have to be ran constantly. This must be done as new workflows are completed on documents or risk losing the associations after 60 days.
     
  • PROBLEM – This impacts all sites in a content database as they will all use this association table. I foresee a problem with maintaining separate expiration times for different sites.  This will now become the responsibility of the operations group to maintain, based the respective customer. If it doesn’t retain this permanently or semi-permanently, then this should be a setting that is configurable at a site or library level, providing the customer the ability to set the expiration period.
     
  • CONCERN – From audit perspective, Sarbanes or other, this is totally unacceptable. NO WARNING to indicate that critical detailed information will be disassociated with its counterpart document after 60 days. Unwary users could be audited 12 months after a document went thru a workflow approval and be none the wiser that their document actually was approved and their workflow history was “cleaned” 10 months earlier…
     
  • CONCERN – One of the biggest selling points for MOSS was the ability to have enhanced workflow on your documents. Almost any company is going to be negatively impacted by this “feature”. Not only financial or healthcare companies, but any financial department within a company is going to be impacted if they use this feature in MOSS. I can’t question the DOD5015 compliance as I do not know if proof of approval history is a requirement. If it is, it certainly is not DOD5015 compliant. 
  
Posted 09-21-2007 10:13 AM by dwollerman

Comments

AutoSponge wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 09-21-2007 1:02 PM

This comes as a shock.  I don't have any test histories that are more than 60-days old but I'll take your word for it.  This will certainly NOT help my case for adopting SharePoint in our compliance office.

Please keep us posted on what you find out.

SharepointUser wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 09-21-2007 1:15 PM

We had the same issue and it impacted us alot and wasted alot of our time and resources. We restored our database, called Micro$oft and spent over $250 and finally and they can't find out the problem!

We found the solution ourselves by figuring out that there is a  "Workflow Auto Cleanup " option available which is available under the MOSS 2007 Central Administration located at "Central Administration > Operations > Timer Job Definitions    

Timer Job Definitions > Workflow Auto Cleanup"

Microsoft stupidly set the option to have the workflows to be deleted after a certain time. By disabling that option, we can aviod our workflow history being deleted. Thank you Microsoft! DUH!

dwollerman wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 09-21-2007 1:20 PM

Awesome, that is good to know, thanks for the tip. Its too bad Microsoft can't offer up that information.

Anil K Sharma wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 09-23-2007 11:30 PM

Such design assumptions should have been documented by Microsoft !!

Karthik wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 09-24-2007 2:07 PM

How do we know "Workflow Auto Cleanup" job is only clearing workflow history and anything else. Is disabling that job the right solution?

dwollerman wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 09-24-2007 2:19 PM

thats the only solution right now. there is no other configuration options. Supposedly Microsoft is supplying a script to auto update the number of days for the cleanup, but thats not a solution either, just a band-aid.

Tort wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 09-24-2007 5:47 PM

Before you guys are so quick to bash Microsoft think about the implications of not creating this job.  Eventually the "Workflow Tasks" list and the "Workflow History" list would grow very large and performance would suffer.  This would also cause major problems eventually and also more support calls for Microsoft.  But you guys are all such big MOSS pro's that you probably knew that already...right?  The ideal solution would be for you to create your own timer job that solves *YOUR* specific needs around compliance and disable the automatic cleanup job that comes OOTB.

dwollerman wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 09-25-2007 7:04 AM

It is not that there is a cleanup process as much as it was not documented by Microsoft that such a thing happens. There are people that rely on the workflow history to prove out their audits. If this knowledge was available then companies might not have 1.) purchased MOSS or 2.) bought a thrid party that handles workflow better that may or may not work with MOSS and this is where Microsoft dropped the ball.  Microsoft purposly did not say anything about this cleanup. They were quick to let everyone know all the benefits of workflow within MOSS though.

The other problem is that Microsoft knows the performance limitations of a sharepoint list. Again ball dropped. They could of easily created another database for workflows or be able to offline / archive workflow history to another storage model. Where is the workflow history for workflows not run in MOSS? Why is running workflows in MOSS different then running workflows outside of MOSS?

The reason this post is here and the reason of such a dramatic title is because it needs to get peoples attention. Companies need to be aware of this implication. It is nice that there is a "workaround", but alot of the compaines that implement MOSS for the easy workflow can't afford to have someone create a specific job for them. It would the same as if Microsoft implemented the recycle bin without configurations of a time / size limit and made people write something custom to configure it.

Mirrored Blogs wrote SharePoint and Windows Workflow Foundation (WWF)
on 09-25-2007 8:01 AM

Body: I was reading an article on WWF vs. LiveLink Workflow and reminiscing with my time working with

Zlatan's Blog wrote Windows Workflow Foundation Updates
on 10-06-2007 5:44 PM

Here's a list of some very useful updates for Windows Workflow Foundation that some of you might

Owner Blog wrote Windows Workflow Foundation Updates
on 10-06-2007 7:37 PM

Here's a list of some very useful updates for Windows Workflow Foundation that some of you might

Anonymous Coward wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 10-15-2007 2:13 PM

Could you provide more details about the workflow cleanup?  Please name the tables in the content-database which are affected and which stored procedures are used to do the cleanup.  Does this cleanup affect the "Workflow History" list or just the "Workflow" and "Workflow Association" tables in the content-database?

Anonymous Coward wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 10-15-2007 3:01 PM

I found my answer here:  forums.microsoft.com/.../ShowPost.aspx

Robert Bogue [MVP] wrote SPWorkflowAssociation.AutoCleanupDays
on 10-16-2007 7:38 AM
Robert Bogue [MVP] wrote SPWorkflowAssociation.AutoCleanupDays
on 10-16-2007 10:38 PM
Andy Burns wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 10-19-2007 8:18 AM

Yup, I've been saying for a while that workflow Audit in SharePoint is not adequate.

In response to Tort - well, yes, I can see why they've written it this way - it fits with the whole list and items idea - but it's also pretty clear that what was written isn't up to many businesses needs.

Would a database table be too much to ask? Or to allow tracking providers to be plugged in, a la WF?

I'd love to have a clear week - I'd write a better auditing system for workflow.

www.novolocus.com/display.php

Dave Wollerman's SharePoint Blog wrote Huge MOSS Workflow Issue... What is Microsoft Thinking!!!! - Part 2
on 11-21-2007 9:14 AM

Huge MOSS Workflow Issue... What is Microsoft Thinking!!!! the original post Ok, recently I came across

Kenneth Scott wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 11-27-2007 8:27 AM

Thanks for sharing this information.   Very surprising.

novolocus.com » Auto-Cleanup of Workflows in MOSS wrote novolocus.com » Auto-Cleanup of Workflows in MOSS
on 01-09-2008 8:53 AM

Pingback from  novolocus.com » Auto-Cleanup of Workflows in MOSS

Karuana wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 03-30-2008 12:43 AM

Thanx to SharePoint User for documenting how to fix this.  We've been using an InfoPath form workflow for some transactions as our pilot project and recently I noticed that information "disappearing".  I just wanted them to TELL me this and not have to a) stress something was wrong b) get stonewalled by tech support, c) stress out my vendor with this repeated question and d) be able to configure it!  Sounds reasonable to me right?  Thanx for the help!

Harmen wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 04-24-2008 2:54 AM

Hi,

maybe this might be of some help: I created a job on the SQL-Server that executes every night this code:

update WorkflowAssociation set AutoCleanupDays = 9999

Solved my problem.

Another question: Somebody has managed to restore the the disappeared workflow information. I found a way by inserting data into the Workflow table, but this takes an awful lot of time.

dwollerman wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 04-24-2008 6:50 AM

Microsoft does not support direct modifications of the SharePoint databases. I do not agree that modifying the SQL tables as a vaild workaround. I know it is the quickest way to get this resolved, but the other options provided by Robert Bogue (www.thorprojects.com/.../708.aspx) and by Joris Poelmans (jopx.blogspot.com/.../sharepoint-workflow-history-cleanup.html) are a more supported workaround.

Keep in mind these posts were to get the community aware, which in turns make Microsoft aware of the impact of this issue. I just hope that this is rethought in the new version of SharePoint.

Thanks.

Ramble On wrote Limitations of MOSS as a Workflow Host
on 04-30-2008 8:47 AM

Hi All I have found some material for you on the SharePoint limitations when hosting Workflow schedules.

Fred Morrison wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 06-03-2008 1:59 PM

I just posted a sample piece of code on the MSDN forums that can be run from inside any workflow that will check and (if necessary) adjust the AutoCleanupDays value of the workflow association to the desired value.

Andy Dang wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 06-13-2008 2:17 AM

It's shocking to find out from MSDN that the associated workflow history is kept upto 60 days for completed or cancelled items -> technet.microsoft.com/.../cc298800(TechNet.10).aspx . It would be good if Microsoft would inform us this feature early enough in the design because we are in the situation that our legal team needs to audit some workflow histories but it's no longer accessible from user or associated. What I've seen so far from the replies were to address how we can prevent this 60 days retention and restore backups to audit these events (also came across with unconventional way by direct access to DB). What I may after is does anyone having tried to use standard APIs to associate or provide a view to see complete audit trails? If there arent such APIs, then it would be catatrophic.

Any lights???

dwollerman wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 06-13-2008 7:34 AM

I here ya, and thank you for responding, I think you are the first one to see (and is possibly living through) the issue and points I was trying to make. Yes it sucks that they are cleaned up after 60 days, but there is no warning of this.

As far as recovering the associations. As you can see there are people that have provided workarounds to help prevent the clean up, but that doesn't fix the issue if they are lost to begin with. The workflow history is still stored in sharepoint and not removed after 60 days, it is only the associations with the item the workflow was executed on that is removed. You can potentially use the standard sharepoint APIs to connect the items in the workflow history list with the workflows associated items to build a report. At this point they are both just list items.

I know that is not the friendliest way to do this and the information in the workflow history list is not formatted in the friendliest way either, but it should provide you with all the information you may need to reconnect them into a report.

lesmantecoooo wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 07-09-2008 1:29 PM

Hi,

Don't you guys realize that the duration of 60 days can be changed from SPWorkflowTemplate.AutoCleanupDays property to meet your data retention requirement? Please be advised that modifying this to long duration can cause performance degradation, as historical data accumulate.

The option is there for you to change to meet your organizational need. I don't think this is a BIG DEAL. You might also want to look at Information Management Policy feature.

Cheers

dwollerman wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 07-09-2008 1:55 PM

We know that this can be change when developing a custom visual studio workflow. We know that we can write custom code to run periodically to change this options as workflow is ran.

It is not configurable for sharepoint designer workflows. It is not configurable when using OOTB sharepoint workflows.

The only way it is configurable or a workaround is to have something (whether it is a workflow or a timer job or a script) developed, which most companies get frustrated with because they just spend big $$$ on the product now they have to spend more money to develop something that should be available.

The big deal part is more related to the lack of warning, documentation and explaination from Microsoft related to this "behind the scences" feature. Most companies were relieved that sharepoint finally had workflow built in, and for the most part it is a well waited for feature and works very well. The problem comes when these companies enact critical business process which require audit history and these companies rely on the associations to give this to them. I don't blame the companies for relying on this since there is no disclaimer or documentation stating that the associations are removed after 60 days.

also, i know that 90% of workflows out there don't need an extreme level of auditing and 60 days should be fine, but there are some that are required to keep it for years and companies are relying on the association for it. Good or bad, thats how it is understood. You can't blame bad consulting either, because alot of these companies walk through standing it up on their own before asking for expert advice. And they don't ask until something has already happened.

Raevean wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 07-17-2008 11:47 AM

If U have to keep a workflow history list for auditing... mayby should you think about creating your own auditing activity which you can use in your workflow develop the way you wanted... I Dont really think its a big issue...

Matthias wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 08-13-2008 8:15 AM

Hi,

i´ve managed to get a workaround, with 2 workflows(24h wait, look if date fits, if not kill workflow and start worflow2. this waits 24h looks etc. ;))

But theres a new problem. After a few days the Item gets an "overflow". Cant Open, Edit, view History or Workflows. I think the WF History is to much for sql, cause i get a "Unknown SQL Exception 191 occured. Additional error information from SQL Server is included below." Eventid 5586. The Statement is to deep!

Any Idea how to get around that issue?

Jeremiah wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 08-18-2008 8:59 PM
dwollerman wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 08-19-2008 10:38 AM

Matthias,

The issue could be with how you are "killing" the workflows. Maybe they are broken processes and the relationship between the workflow and the item was killed in the process causing sharepoint to error because it cannot find the information to join them.

drewdevo wrote Workaround for Missing Workflow History
on 08-27-2008 3:02 PM

Description Our company discovered during an internal audit that workflow history had been removed from

SpittingCAML » Another reason to ditch vanilla MOSS Workflow and create your own! wrote SpittingCAML » Another reason to ditch vanilla MOSS Workflow and create your own!
on 10-19-2008 5:16 AM

Pingback from  SpittingCAML » Another reason to ditch vanilla MOSS Workflow and create your own!

Juan Larios wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 10-27-2008 10:42 AM

SharePoint Workflow Audit solution available for download!

www.imaginets.com/.../sharepoint-workflow-audit-solution-trial-download.aspx

Juan

Huge MOSS Workflow Issue… What is Microsoft Thinking!!!! - Part 2 | Berazinsky User Profile wrote Huge MOSS Workflow Issue… What is Microsoft Thinking!!!! - Part 2 | Berazinsky User Profile
on 01-12-2009 2:06 PM

Pingback from  Huge MOSS Workflow Issue… What is Microsoft Thinking!!!! - Part 2 | Berazinsky User Profile

Anthony Mallgren wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 03-17-2009 1:19 PM

You guys are putting too much effort into a problem that has a very simple solution:

mosspro.blogspot.com/.../solution-to-sharepoint-errors-due-to.html

Brandt Fuchs wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 03-24-2009 9:30 AM

I've been creating solutions that work around this for a while, although I must admit I was unaware of this limitation until recently.  The way I write my workflows is to store any necessary audit information as fields within a list item.  This way the history is not lost, and by using custom XSLT views you can see the audit information in a much cleaner, more presentable way.

Prashant Raut wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 04-22-2009 5:56 AM

Hi,

I lost my workflow history and Workflow task data due this limitation and changed the code to make autocleanup as much as i want but how do i recover workflow task data. I am able to recover workflow history data which is hidden.

Association has been lost after 60 days and now need to reassociate the same. Setting autocleanup days more than 60 days to previously lost data won't help.

dwollerman wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 04-22-2009 5:33 PM

This information is stored in the content database. if you have backups from the timeframe you think the information was available, then technically you should be able to recover through a database recover process. However, getting this information importted back into the association list will tend to be the more impossible scenario. I would expect you would have to wrtie a tool to extract information from the association list in the recovered database and import into association list on live site.

Keep in mind you may have to link this infromation up to the workflow history as well as the item/document identifiers. For the most part this should not have changed, but you never know.

eric wrote re: Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
on 06-25-2009 10:20 AM

Just write all the workflow auditing into a custom list

Add a Comment

(required)  
(optional)
(required)  
Remember Me?
Need SharePoint Training? Attend a SharePoint Bootcamp!
Posts (c) their respective authors. Everything else (c) 2009 SharePoint Experts, Inc.