How to set the deadlock priority for a maintenance task

• ianharris2

  Mr or Mrs. 500

  Points: 513

  More actions

  September 2, 2016 at 6:02 am

  #329796

  We have two maintenance tasks which occasionally conflict resulting in a deadlock. One runs a History Cleanup Task and the other runs our Transaction Log backups.

  When a deadlock occurs, the Transaction Log backup task is chosen as the deadlock victim. I would like to set the Deadlock Priority so that the history cleanup task is always chosen as the victim. However I cannot see where to do this either in the Maintenance Plan properties or in the SQL Agent job properties.

  Is it possible to do this?

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  September 4, 2016 at 9:51 am

  #1899125

  You can use

  SET DEADLOCK_PRIORITY n

  n=-10 lowest

  n=10 highest priority

  Read more for the priority here https://msdn.microsoft.com/en-us/library/ms186736.aspx

  Igor Micev,My blog: www.igormicev.com