Triggers Stopped Working

[system]

How to handle when schedules are not triggered as expected due to deadlock issue in the database ?

Deadlock can be found in the database by

1. Running the below query

select spid,blocked,cpu,physical_io, [status],loginame, t.text,[program_name],cmd from sys.sysprocesses p cross apply sys.dm_exec_sql_text(sql_handle) as t where db_name(p.dbid) ='' AND [status] <> 'sleeping'

2. Using SQL profiler - Finding And Troubleshooting SQL Server Deadlocks

Root Cause: Most likely caused by some deadlocks in SQL. It is known to occur as a result of having a large number of items in SQL tables.

Orchestrator utilizes Quartz as a scheduling mechanism. And Quartz has a known issue where it can suffer very rarely from a race condition in certain cases. This can be exacerbated by poor SQL performance, which a large number of items in tables cause, as it extends the timeframes of transactions and increases the likelihood of the race condition.

The following solutions, best practices can be applied to mitigate such issues:

1. Set is_read_committed_snapshot_on to 1 if not already set. The below query can be executed -

• ALTER DATABASE UiPathDatabaseName SET READ_COMMITTED_SNAPSHOT ON

2. Rebuild all the indexes of UiPath database
3. Archive/Purge the data from tables dbo.logs, dbo.UserNotification, dbo.TenantNotification and dbo.Jobs

It is recommended to perform step 2 and 3 as maintenance activities on some regular intervals.

After performing these steps, recycle Orchestrator AppPool and restart Orchestrator on both the nodes. Note that restarting website will bring down the application for couple of seconds.

The Orchestrator will not function until the rebuilding is done. The Orchestrator should be stopped, rebuilt and start. The Orchestrator can be rebuilt by executing the below queries

• Rebuild all indexes

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'

• Rebuild all indexes on a particular table

alter index all on dbo.Logs rebuild

• Rebuild a particular index on a particular table

alter index IX_Job on dbo.Logs rebuild .