@rtripathi28 

I took a look at the dbo.mArchive stored procedure that is available at:

And it looks like it does not look to do table or row locks.  It looks to try it in a transaction and if anything fails (‘With NOWAIT’), then rollback the transaction.  Not SQL expert, so open to corrections.

Is there a need to run it during business hours?  Instead of planned downtime/maintenance period? Or a time where there would be least active users using system?

Archiving should only affect workflow reporting data or ServerLog schema for workflows in Completed state, so should not affect normal users actions (unless they are viewing Completed workflow reporting data at the same time).  Although if you plan to run this during business hours, the archiving process may utilize additional resources (cpu, memory, disk read/write) on top of the normal K2 server operations, so may cause bottlenecks and slowdowns for users.

Since archiving is copying data from K2 database to your Archive database according to your date range and deleting it after it is moved, how long it takes will likely depends on many factors:

• how many completed instances are in that date range
• system resources (CPU, memory, disk read/writes)

You can also test out the archive process before actually doing it by creating a backup of the current K2 database, restoring in on another SQL instance somewhere else and trying the archiving process there first.

If you can’t perform this archiving action during a downtime/maintenance period, I would at least pick a time with the least amount of active users.  Then you can use the datetime range from your testing to plan accordingly as to how much to archive per run.

Is there a need to run it during business hours?  Instead of planned downtime/maintenance period? Or a time where there would be least active users using system?

We are having large database where need to run it many times or as required and working on a strategy with full proof planning to avoid risk of failure.

Hi @TinTex 
Any more feedback you could add please?

When i was running it with large number of rows in the database. I was getting error “Transaction log full”, other than this i does not have any issues. Kindly provide me the recommendation so that i will not have this issue in future.

@rtripathi28 

Is the K2 database currently using the Full Recovery model and the transaction log is set to autogrow?

As the archiving stored procedure runs in a transaction, it will write to the transaction log database and due to the large amount of records it is processing will fill up the transaction log database.  You can try increasing the available space disk space in SQL (perhaps doubling it) to allow for the transaction log database growth and archiving process to complete.

Then perhaps backing up or truncating the transaction log database and shrinking it to reduce its size.  If necessary you can return the disk spaces to previous values after archiving has been completed.

https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/