SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DEADLOCK_PRIORITY vs System Processes


DEADLOCK_PRIORITY vs System Processes

Author
Message
Abrar Ahmad_
Abrar Ahmad_
SSC Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 Visits: 1305
During execution of a critical application process; a system process (e.g db auto shrink) evoked and the process prolonged to manually rollback.


Is DEADLOCK_PRIORITY can be useful against system processes?
We need to disable db auto shrink, but .... any other solution? Cool


colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11655 Visits: 715
not quite sure what you're asking but yes "system" processes can evoke deadlocks - that's why you have to configure your server to avoid such issues. Index rebuilds for instance can also cause deadlocks.
I don't think you can alter the priority for system processes, you could try altering your sessions but I figure that might be risky.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Abrar Ahmad_
Abrar Ahmad_
SSC Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 Visits: 1305

...
I don't think you can alter the priority for system processes, you could try altering your sessions but I figure that might be risky.



Discuss how?

In general I want to get rid of Blocking/Dead locking ultimately, as if a process which may take more than 1 hour for execution and a single sql statement which can trigger out deadlock and would may cause/stop the long running process...!

:-)


colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11655 Visits: 715
ah well if it's application then it's processes/code that you need to change - if it's system stuff then you need to find a quiet time. I support real 7 x 24 hour systems so things like index rebuilds are quite painful.
You can set the deadlock priority for your session ith a set command - check it out in BOL. However it doesn't stop deadlocks - just allows you to favour one session over another as the deadlock victim which is why I've never used it to date.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search