[DBCleaner] Timeout error when i delete a large number of processes

[DBCleaner] Timeout error when i delete a large number of processes

  
Forge Component
(36)
Published on 23 Nov (3 weeks ago) by Johan den Ouden
36 votes
Published on 23 Nov (3 weeks ago) by Johan den Ouden

Hi all,

I cannot delete a large number (>50000) of processes from DBcleaner, because i got an timeout error (See below)


Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at OutSystems.HubEdition.Extensibility.Data.ExecutionService.BaseExecutionService.ExecuteNonQuery(IDbCommand cmd)
   at OutSystems.Internal.Db.Command.ExecuteNonQuery(String description, Boolean isApplication, Boolean skipLog, Boolean applyTransformationsToParameters)
   at OutSystems.HubEdition.RuntimePlatform.ProcessDeletion.DeleteProcesses(Transaction trans)
   at OutSystems.RuntimePublic.Processes.ProcessDeletion.DeleteProcesses()
   at OutSystems.NssBPT_API.CssBPT_API.MssProcess_BulkDelete(DateTime ssProcessesOlderThan, Int32 ssProcessDefinitionId, Int32 ssMaxDeletedProcesses, Boolean& ssHasMoreToDelete)
   at ssDBCleaner.RssExtensionBPT_API.MssProcess_BulkDelete(HeContext heContext, DateTime inParamProcessesOlderThan, Int32 inParamProcessDefinitionId, Int32 inParamMaxDeletedProcesses, Boolean& outParamHasMoreToDelete)


Any Ideas?


Matheos Polydorou

Hi Matheos

I would say you can either:

  • Pick smaller time windows. For example, deleting one week at a time, back from the oldest ones to the newest ones. For this, start with an arbitrarily high number (say 20 weeks or 50 weeks) and then subtract 1 or 2 weeks in each following run.


  • Increase the default query timeout in your environment. I would use this only as last resort, and never in a production environment - it's not worth the risk.


Let us know how it goes!

Regards,
Acácio

Hi Matheos,

Another option could be implementing your own maintenance procedures using the BPT API.


Cheers,

Tiago.



Hi Matheos

Have a look at this component: DBAutoMaintainer. It automates most of the maintenance tasks, including clearing BPT PRocesses.

There are also these ones (BPT Terminator, BPT Utils) that focus on the BPT stuff.

Also, like Tiago mentioned, you can easily build your own version as well by using the BPT API. Perhaps have it delete a smaller number of records in a loop and commit after each loop.

Hope this helps.

Hanno

Hi all,


Thanks for the help. I solved the problem by changing the deleted processes  number from 1000 to 10.


Matheos Polydorou