Scheduled Jobs Getting Skipped Due To Deadlock On Database

Why does scheduled jobs get skipped because of deadlock on database?

  1. Follow the steps given on below links to find culprit process,
  2. Check what this process does and either rollback or kill the process.
  3. Database fragmentation can be a reason for deadlock or database slowness. Please run below query on UiPath database to find fragmentation
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent DESC



Recommended Database Maintenance Activities

  1. Rebuild all the indexes with avg_fragmentation_in_percent > 30 % and REORGANIZE indexes with avg_fragmentation_in_percent between 5% to 30% on regular basis
  2. Read_committed_snapshot_on should be, Run below query to check the value,

    SELECT sd.is_read_committed_snapshot_on
    FROM sys.databases AS sd
    WHERE sd.[name] = '';


    To turn it ON -
    • Right Click on database Name → Properties → Options → Is Read Committed Snapshot On (Under Miscellaneous) → Set this value to TRUE
Or
  • Run below query : ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON
  1. Perform archival / purging operations on UiPath database, specially on tables - dbo.UserNotification, dbo.TenantNotification, dbo.Logs and dbo.Jobs.