Click here to Skip to main content
12,549,156 members (48,806 online)
Click here to Skip to main content
Add your own
alternative version


1 bookmarked

Setting deadlock priority in SQL Server

, 19 Sep 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Explains how to run a script with higher priority to avoid it getting terminated in a deadlock situation.


Sometimes critical jobs might terminated because they faced a deadlock situation and was choosen as the deadlock victim by SQL Server. The other process which was allowed to continue may be a stored procedure call from a web page, and a failure on that would have impacted a single user's session. But the failed job might impact all users of the application. In these scenarios it is ideal to run the critical jobs with a higher deadlock priority so that they are not chosen as deadlock victims.


We can set this connection property to appropriate level and SQL Server will terminate one with lowest priority. This feature is available from SQL Server 2005 onwards.

By setting the deadlock priority to high we can prevent our script being terminated as deadlock victim as shown below.


-- Your script here


You can give a numeric priority from -10 to 10 as well incase you want to set different priorities for a set of scripts which are expected to execute simultaneously with a chance of deadlock. 

Please refer to for more details.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Saral S Stalin
Technical Lead
India India
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionRegarding tip Pin
Abdul Quader Mamun19-Sep-13 23:58
memberAbdul Quader Mamun19-Sep-13 23:58 
AnswerRe: Regarding tip Pin
Amol_B23-Sep-13 20:51
professionalAmol_B23-Sep-13 20:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161021.1 | Last Updated 19 Sep 2013
Article Copyright 2013 by Saral S Stalin
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid