Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DEADLOCK_PRIORITY vs System Processes Expand / Collapse
Author
Message
Posted Monday, January 3, 2011 11:12 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 211, Visits: 1,111
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?

Post #1042167
Posted Thursday, January 6, 2011 3:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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/
Post #1044066
Posted Tuesday, January 11, 2011 4:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 211, Visits: 1,111

...
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.



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...!




Post #1045800
Posted Tuesday, January 11, 2011 1:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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/
Post #1046134
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse