You are viewing limited content. For full access, please sign in.

Question

Question

Excessive growth of the Workflow database

Updated August 29, 2018
asked on August 21, 2018

We have a client that has the tool installed for more than 5 years, always with the same number of instances of Workflow and users.

The Workflow database has always had a normal growth, however for 3 months it has grown disproportionately, going from a weight of 60 GB to 80 GB and later to 115 GB. This happens from one moment to another, in a matter of 3 days.

I have applied several querys in the workflow database, in order to determine which tables consume the most space, the result is:

If we add the totality of the tables, the weight of them does not exceed 60 GB so we proceeded to run another query, in which it indicates;

In the previous image, it is indicated that database size = 113130 MB, unallocated space = 53992 MB

What is the space consumed in unallocated space? Is there a way to free that space? This is because if we release that space the database will return to its normal size

 

 

 

0 0

Replies

replied on August 21, 2018

SQL database don't automatically shrink because the operation of expanding is fairly expensive. It looks like your Workflow only uses about half of the 115GB, so most likely it is deleting older data. The size of the _log tables is bigger than expected, but, without looking in more detail, it's hard to say if they're big because there was an infinite loop and WF hasn't caught up with deletion or if there is constantly more data than WF can delete.

You can start by increasing the database cleanup window in the WF Admin Console (under server timers).

As for shrinking the database, that can be done as part of backup operations.

1 0
View 5 previous replies
replied on August 21, 2018

Hi Miruna, 

Thanks for the response, we have enabled the maintenance tasks for 2 months since they were unmarked, however we have not been successful, now this is how it is;

more than that information we can not eliminate since it is essential to have at least 1 year's history.

What is not clear to us is why this behavior, if for years the growth of the database had been maintained. We want to rule out that it is something from the Laserfiche version or similar, however, even with support, we have not been able to find a valid reason for the client

Any other recommendation?

 

0 0
replied on August 21, 2018

The default setting for completed instances (the second set of numbers in your screenshot) is 30 days. Your setting is 365 days. That means all the history of every instance will be preserved in the database for a whole year after it completes.

So in that respect, it doesn't look like Workflow is behind on deletion. Do you know why the customer chose to keep data for a year? Are they doing reports on it? Are they using it for troubleshooting instances that terminated more than 30 days ago?

0 0
replied on August 21, 2018

You can run a statistics report to get more information on how the instance count breaks down by workflow definition. That might give you more insight into whether there might be other possible issues.

0 0
replied on August 21, 2018 
The client has a large audit department, which can request information about the process of a document at any time.

It has always been unchecked, maintaining the infinite history of information, 2 months ago we recommend activating these maintenance to verify if the issue of database size was resolved, however there was no considerable change and the database continued to grow disproportionately
replied on August 21, 2018

The client has a large audit department, which can request information about the process of a document at any time.

It has always been unchecked, maintaining the infinite history of information, 2 months ago we recommend activating these maintenance to verify if the issue of database size was resolved, however there was no considerable change and the database continued to grow disproportionately

0 0
replied on August 24, 2018

Thanks for the post, please consider the following adicional info:

On this client, the workflow instance can be running for years and every instance will be running or "waiting" for several months.

The cleanup window has always been in 365 days after completion, and for over 5 years there has not been any issues and the database growth has always been with in a few GB per month, but for the last 3-4 months the growth jumped to 30GB on a few days and another 30GB on another week.
There are no new workflows created, there are no indefinite loops, there are no new starting rules.  Running instances are 15,000, same average that we have always had. 

We have 2 basic inqueries:
1. how to reduce the space in the hard drive

2. why did this happened and how can we prevent it from happening again.

0 0
replied on August 28, 2018

From the screenshots above, it looks like the database is only half full, so it can be shrunk to reclaim the space. From the support case, it looks like maybe a large part of the space is used by the SQL database's log file. That can also be shrunk as part of regular backups.

Like I said above, it's hard to tell from just these screenshots. We'd need to do more investigation to see what's going on. You might want to monitor its growth (and individual table sizes) over a few weeks.

Looking over the support cases, it looks like the database was restored from backup around the beginning of July. Depending on how old the backup was, it's possible that WF has to catch up on deleting older instances, so it ended up bloating up the log file. But that's just a guess.

It's probably best that you take this up with Tech Support for more investigation as there's some conflicting information in this thread on what the maintenance settings are and when they were changed.

0 0
replied on August 29, 2018

Hi Miruna, 

 

We have a case with Support, however it has been difficult for us to find the solution to the problem.


We have discovered something that can help us.

In SQL there is an option to condition the automatic growth of databases, we have seen how in all databases this growth is limited to 1 MB, and in the case of logs to 10 MB, however with this particular client these options are marked to a growth of 45% automatically:

Workflow Database:

 

Laserfiche Database: 

Yesterday we did a maintenance of the database and it considerably reduced its size, however today it already dawned with a much higher weight, we believe that the reason is the automatic growth that has configured this database.

Question; Is this behavior normal?

Why is it that these specific databases have an automatic growth of 45%?

What happens if we limit that growth?

It seems to us that what is indicated is related to what was mentioned at the beginning of the post, the unallocated space.

I appreciate the help you can give us

0 0
replied on August 29, 2018

Again, I don't have enough information to guess if that behavior is normal. Depending on the load and what the workflows do, it can be.

As for the SQL options, you'd have to check with the customer's database administrator. Workflow does not make any changes to the database's options.

If you limit the growth and the limit is reached during normal operations, SQL transactions will start failing because they cannot be written to the log. You could measure the growth and figure out a shrinking schedule that allows you set a limit on how much logs could grow. Various blogs on SQL administration recommend against shrinking the database a lot.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...