Out of the ~50 instances I currently manage, I have two that each have one deadlock almost every night involving "INSERT INTO msdb.dbo.sysjobhistory" (always the victim) and "DELETE FROM msdb.dbo.sysjobhistory" called by "EXEC msdb.dbo.sp_purge_jobhistory", both sessions run by the SQL Server Agent service account. I have never had such a deadlock on any of the other instances, nor at any previous jobs. One each of these servers, the deadlocks occur at different times during the night (1:30 Saturdays run by a weekly schedule for sp_purge_jobhistory or 2:01 run by a step in the nightly schedule for syspolicy_purge_history).
There doesn't seem to be anything significantly different between these two "problem" instances and any of the others. One is a 2012 SP3 CU3 test server and the other is 2012 SP2 production instance.
Any ideas as to how I can prevent this?
Best Answer
I wrote this (a much simplified version of the code in @Taiob's blog post he linked to), and then call it from a job instead of the Microsoft-provided sp_purge_jobhistory:
It cut us down from having over 1 million rows in msdb.dbo.sysjobhistory to only about 1/4 as many, and the deletes are no longer involved in deadlocks.
Thanks to @Taiob and @ScottHodgin for their input!