Database maintenance

I think this is a very valid question, and one I'm wondering a bit about as well.

I think that most people will need to create a sensible policy taking into account how much time you want to leave data there, and possibly create an archive database as well. (I wonder if the sql persistence service database would need this, as well, or whether it is self-cleaning).

What worries me more is not that no sample policies have been brought forward by MS (I think that a decent policy would be easy to create on our own); but rather the fact that as far as I can see, no documentation on the tracking database schema has been provided (yet), and more importantly, the logic included in the database doesn't include such basic tools as clean up procedures for cleaning up stale data on top of which you could schedule clean up jobs.

At least I believe that the latter would be an important addition and would like to know if there are any plans for this.

TomasRestrepo at 2007-9-8 >

Thanks for answering; the partitioning stuff seems interesting. A few questions, though:

1. Is partitioning done on real time (or rather at the point of tracking) by the SqlTrackingService? If so, any reason why this design was chosen over an off-line, database partitioning scheme? In particular, will this require special privileges on your tracking database for the workflow host to be able to create tables as needed?
2. Does the SqlTrackingQuery stuff support partitioning? If not, I think it really reduces the value added by it.
3. I would like to put in a suggestion for a clearly documented database schema for the tracking stuff, as well as at least sample procedures for purging and archiving data in it. I'm happy if this doesn't ship with the product, but at least providing sample scripts on top of which to start would be a real help here.

TomasRestrepo at 2007-9-8 >

Thanks for the comprehensive answer, it is much appreciated. It is excellent that we can run the PartitionCompletedWorkflowInstances procedure on our own, that is definitely gonna help, as I'm sure some of our customers will definitely prefer it that way. It's also great that the SqlTrackingQuery stuff will look at the partition tables, that's what I was hoping for :)

TomasRestrepo at 2007-9-8 >