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

Question

Question

What tables does the Workflow Administration clean up when all tasks are checked under Trigger Settings?

Workflow Administration
Updated May 6, 2022
asked on December 5, 2014 • Show version history

Could you please list all the tables that are cleaned up from the Workflow Administration database cleanup trigger? 

 

Customer has concerns regarding tables "Search_Activity_Log" & "Search_Activity_Message_Log"? He believes they are not getting cleaned up. 

0 0

Answer

SELECTED ANSWER
replied on December 5, 2014

All the tables with names in the search_*_Log format. What version of Workflow are we talking about?

0 0

Replies

replied on December 5, 2014

I appreciate your assistance. The issue was that it was timing out. We will manually run a clean up on the tables to fix this issue.

0 0
replied on December 5, 2014

Is this 8.3?

0 0
replied on December 5, 2014

They have 9.1.1.365. Do you have recommendations of how to manually clean those tables?

0 0
replied on December 5, 2014

Search_Activity_Log & Search_Activity_Message_Log

0 0
replied on December 5, 2014

How many rows are in Search_Activity_log? How did you determine that cleaning up timed out?

(For 9.1, the cleanup procedure runs (by default) hourly every day from 2AM to 10PM and deletes batches of rows from the table, not the entire contents. The default configuration is only deleting data older than 30 days)

0 0
replied on December 5, 2014 • Show version history

They are wanting to clean up the database and then originally found these two tables were not getting cleaned up.

They are receiving an error within Event Viewer stating that there is a timeout error. They have definitely have over a couple of million rows in each table. 

 

0 0
replied on December 5, 2014

Can you post the error, please?

 

Depending on their load, a couple of million rows may be reasonable for data less than 30 days old. 

They can manually run the cleanup_tracking_log procedure (it clears out data from the search_instance_log table, which cascades to the other tables). They'll need to specify the date before which they want data deleted.

0 0
replied on December 8, 2014

Here is the Event Log below.

 

Timestamp: 2014-12-05 08:05:05.439

 

Local Time: 12/5/2014 2:05:05 AM

 

Category: Errors

 

Machine: SVLF

 

Severity: Error

 

Application Domain: Laserfiche.Workflow.Service.exe

 

Process Id: 3064

 

Process Name: C:\Program Files\Laserfiche\Laserfiche Workflow 9\Laserfiche.Workflow.Service.exe

 

Win32 Thread Id: 7136 -

 

Thread Name:

 

Message: <Exception handlingInstanceId="62ba6e6a-d3e2-4bc6-bd23-deaf8c354a90">

  <Description>An exception of type 'Laserfiche.Connection.LFSOException' occurred and was caught.</Description>

  <DateTime>2014-12-05 02:05:05Z</DateTime>

  <ExceptionType>Laserfiche.Connection.LFSOException, Laserfiche.Workflow.Common, Version=8.3.0.0, Culture=neutral, PublicKeyToken=d8428ff9263e6cda</ExceptionType>

  <Message>Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.</Message>

  <Source />

  <HelpLink />

  <Property name="LFROError">False</Property>

  <Property name="LFSOError">False</Property>

  <Property name="ErrorCode">780</Property>

  <Property name="TargetSite">&lt;undefined value&gt;</Property>

  <Property name="HResult">-2146233088</Property>

  <StackTrace />

  <additionalInfo>

    <info name="MachineName" value="SVLF" />

    <info name="TimeStamp" value="12/5/2014 8:05:05 AM" />

    <info name="FullName" value="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

    <info name="AppDomainName" value="Laserfiche.Workflow.Service.exe" />

    <info name="ThreadIdentity" value="SIERRA\smahanna" />

    <info name="WindowsIdentity" value="NT AUTHORITY\SYSTEM" />

  </additionalInfo>

  <InnerException>

    <ExceptionType>System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>

    <Message>Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.</Message>

    <Source>.Net SqlClient Data Provider</Source>

    <HelpLink />

    <Property name="Errors">System.Data.SqlClient.SqlErrorCollection</Property>

   <Property name="ClientConnectionId">90d5f5f4-b9f4-47f0-8ed7-7a3e0992a2d9</Property>

    <Property name="Class">11</Property>

    <Property name="LineNumber">0</Property>

    <Property name="Number">-2</Property>

    <Property name="Procedure" />

    <Property name="Server">svlfsql</Property>

    <Property name="State">0</Property>

    <Property name="ErrorCode">-2146232060</Property>

    <Property name="HelpLink.ProdName">Microsoft SQL Server</Property>

    <Property name="HelpLink.ProdVer">11.00.2100</Property>

    <Property name="HelpLink.EvtSrc">MSSQLServer</Property>

    <Property name="HelpLink.EvtID">-2</Property>

    <Property name="HelpLink.BaseHelpUrl">http://go.microsoft.com/fwlink</Property>

    <Property name="HelpLink.LinkId">20476</Property>

    <Property name="System Call">False</Property>

    <Property name="WasInvocationError">True</Property>

    <Property name="Trace">Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

cleanup_tracking_log_chunk

Name: @cutoff_date

Type: DateTime

Value: 11/25/2014 8:00:00 AM

Name: @chunk_size

Type: Int32

Value: 500

</Property>

    <Property name="TargetSite">System.Object ExecuteCommand(System.Data.Common.DbCommand, System.Delegate, CallTypes)</Property>

    <Property name="HResult">-2146232060</Property>

    <StackTrace>   at Laserfiche.Workflow.Database.CommandExecutor.ExecuteCommand(DbCommand command, Delegate method, CallTypes type)</StackTrace>

    <InnerException>

      <ExceptionType>System.ComponentModel.Win32Exception, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>

      <Message>The wait operation timed out</Message>

      <Source />

      <HelpLink />

      <Property name="NativeErrorCode">258</Property>

      <Property name="ErrorCode">-2147467259</Property>

      <Property name="TargetSite">&lt;undefined value&gt;</Property>

      <Property name="HResult">-2147467259</Property>

      <StackTrace />

    </InnerException>

  </InnerException>

</Exception>

------------------------------------------

 

Timestamp: 2014-12-05 08:05:05.439

 

Local Time: 12/5/2014 2:05:05 AM

 

Category: Errors

 

Machine: SVLF

 

Severity: Error

 

Application Domain: Laserfiche.Workflow.Service.exe

 

Process Id: 3064

 

Process Name: C:\Program Files\Laserfiche\Laserfiche Workflow 9\Laserfiche.Workflow.Service.exe

 

Win32 Thread Id: 7136 -

 

Thread Name:

0 0
replied on May 3, 2022

Hi Miruna,

We come across similar issue on 10.4 workflow server as we have seen issue that the transaction log disk had filled up as log backups were not running. and found root cause as issue started at 2 am on Sat 30th April, when the Laserfiche Workflow Server issued a delete SQL statement on the LFWorkflow database, which was then rolled back.  

The roll back didn’t complete and the transaction was left open for days, which seemed to cause the transaction log backup job to hang and led to the log growing and running out of space.

 

On workflow server we can see error event below -

 <Message>Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</Message>

 

Does this needs manual clean-up and is there any solution for this issue or recommendations to be followed?

 

0 0
View 6 previous replies
replied on May 3, 2022

You shouldn't have to as Workflow will try again during the next cleanup window. Workflow deletes in small batches in order to keep the cleanup operation from affecting the performance of running workflows, so if your database is very large, you can increase the frequency of the timer. But if it's just the one time error when SQL was out of disk space, you likely won't have to and Workflow will catch up on its own.

0 0
replied on May 3, 2022

thanks Miruna for response but what we observed is - when this failed the roll back didn’t complete and the transaction was left open for days, which seemed to cause the transaction log backup job to hang and led to the log growing and running out of space.

0 0
replied on May 5, 2022

Sorry to be a chaser Miruna but do you think  below is expected behaviour with this clean job or a issue that needs support case raised. can you advise please?

"when clean job failed , it initiated roll backs that didn’t complete and the transaction was left open for days, which seemed to cause the transaction log backup job to hang and led to the log growing and running out of space, query is why it ran roll back when job failed?

0 0
replied on May 5, 2022

From your description, it sounds was a side-effect of running out of disk space and SQL couldn't properly rollback the incomplete transaction.

Do you have more info on what sort of transaction was being rolled back? I don't see any explicit rollback commands in Workflow's SQL commands for clearing out the old instance data.

0 0
replied on May 5, 2022 • Show version history

looking at database audit below are findings - 

 

For the run on 30th April @2am it looks like it ran the clean-up proc but then immediately tried to roll back the transaction, which then got stuck in a roll back status until morning @3am when we restarted SQL.  The roll back was stuck waiting for it to synchronise to the secondary database (HADR_SYNC_COMMIT wait type).  DBA can see that subsequently more and more other processes started to get stuck with this same wait type of HADR_SYNC_COMMIT, both Laserfiche processes and maintenance tasks including log backups, which resulted in the transaction log growing and eventually filling the F: drive.  Since the SQL restart we’ve not had a repeat of these HADR_SYNC_COMMIT waits.

 

Wondering Is it because of default timeout below (300 seconds) Laserfiche decided to roll back rather than commit on this occasion?

 

 

 

 

 

Also don’t see the job initiated after 30/04 as Last run on admin console still shows 30/04 below , so wondering this needs fixing or not?

 

 

 

 

I have raised ticket with service provider thinking might need further investigation and fixing.

0 0
replied on May 5, 2022

Hm, that screenshot shows the cleanup query finishing in 15 minutes on the 30th. The store procedure for cleanup does not run a transaction.

What  do you mean about the secondary database? Though, at this point, it does sound like a support case might be the better way to investigate this issue.

1 0
replied on May 5, 2022

thanks i raised a service provider ticket. will advise them to raise a support case.

 

0 0
replied on May 6, 2022

Miruna - I have raised a ticket with service provider which yet to be picked up but as this is our prod system and looks this job is hang at 30/04. Not sure if its just console or clean up process stopped working in database. How this can be enabled or need clean up manually, what impact this process not working could have on the system? any advise please? 

0 0
replied on May 6, 2022

Has the service been restarted since? Restarting the Workflow Server service should clear up any SQL connections that might be confused.

 

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

Sign in to reply to this post.

Details
Related Posts
Loading ...