Managing Blocking in sQL 2000 ? Need a code to determine Deadlock

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  October 27, 2006 at 8:06 am

  #62223

   Hi All,

  I have got a very busy database, and a lot of users seem to run queries which then result into locks being hekld on the tables.

  I have read articles on SQL 2005 to manage blocking, i.e using Try & Catch.

  I was wondering if anyone knew of a way to Prevent blocking/deadlocks in ones code, one way is to specify Nolock etc but atimes blocking still occurs..

  Thanks in advance 

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)
  

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  October 27, 2006 at 8:44 am

  #668525

   John,

  Try/Catch blocks in SQL Server 2005 are used for error trapping.  I have not seen any information relating them to blocking.  Can you post the link where you read that Try/Catch blocks assist in alleviating blocking?  As far as I know, keeping blocking and deadlocks to a minimum in SQL Server 2005 uses the same methods as in SQL Server 2000.  That is, keep your transactions as short as possible.  Blocking is a normal part of the SQL Server locking mechanism and is to be expected.  The goal is to keep the blocking as short as possible.  I would say that if you are experiencing a large amount of blocking and you have many users who are able to run queries, that your users may not be writing efficient queries.  I would capture some of the queries using Profiler during times of heavy blocking and find out which users are submitting in-efficient batches. 

   

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• Johan Bijnens

  SSC Guru

  Points: 135549

  More actions

  October 27, 2006 at 8:58 am

  #668528

  you may want to add following startup-parameters to your sqlserver so you get a glimp regarding deadlocks...

  -T1204

  -T3605

   

  to activate this without stop/start :

  --dynamisch activeren

   dbcc traceon(1204, 3605, -1)

  but add the parameters for startup or the feature will not be active when you stop/start sqlserver

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me