Why does scheduled jobs get skipped because of deadlock on database?
- Follow the steps given on below links to find the culprit process:
- Check what this process does and either rollback or kill the process.
- Database fragmentation can be a reason for deadlock or database slowness. Please run the below query on the UiPath database to find the 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
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
- Rebuild all the indexes with avg_fragmentation_in_percent > 30 % and REORGANIZE indexes with avg_fragmentation_in_percent between 5% to 30% on a regular basis
- Read_committed_snapshot_on should be, Run the 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
- Perform archival/purging operations on the UiPath database, especially on tables - dbo.UserNotification, dbo.TenantNotification, dbo.Logs and dbo.Jobs.