AnsweredAssumed Answered

Executing SP garbageCollectTaskPersistence from a SQL client Or from a batch script

Question asked by Tushar_Desai on Aug 10, 2016
Latest reply on Aug 12, 2016 by william.k.lee

I have a question around perform garbage collection "Cleanup Submitted Tasks' from User Console vs a SQL Client.


We like to schedule the garbage collection to cleanup taskpersistence database. We tried running the "Cleanup Submitted Tasks' from User Console, however, the task is too slow. The Cleanup task is processing or cleaning roughly 650 tasks per hour.


We also tried running the garbage collection from a SQL Client and also through the batch script using 'sqlcmd' by invoking the storedProcedure. It performs better cleaning roughly 1500 tasks per hour.


However, the issue is when the storedProcedure is executing and performing a cleanup, we could not run any other queries. Any query like a simple 'select' statement run against 'tasksession12_5' table are left in executing state forever from the SQL Client . It seems like running the garbage collection storedProcedure from SQL Client locks the table whereas running the "Cleanup Submitted Tasks' from User Console does not lock the table.


set @cutoff_time1 = (select DATEADD(day, DATEDIFF(day, 14, getdate()), 0))

EXEC garbageCollectTaskPersistence

    @ime_id = 1,

    @cutoff_time = @cutoff_time1, (anything before 14 days of data are cleaned out)

    @audit_timeout_days = 1,

    @chunk_rec_count = 500


Does anyone have any input on how to resolve this issue running the storedProcedure without locking the database ?