SQL 2K locking issue

[wbodger]

OK, here is my situation.

I have SQL Server 2K sp4 running on a Windows 2003 Server box. Then, we have Onyx (an older CRM) using SQL Server. Every now and again I get blocking problems. When I track down the offending process and kill it it sometimes helps for a minute before it blows up even more and sometimes it is of no help at all. I have tried to track down the root of this problem, but fear I am not searching in the right place. In general, when trying to track down a problem like this, where do you start and what steps do you take? Thanks!

WB

 

[mrdenny]

You have to look in the ERRORLOG file to see what the objects are that are causing the problem. Sometimes adjusting the indexes on the tables can be adjusted to improve performance. Other times you'll need to modify the processes so that the deadlock doesn't happen any more.

The way a deadlock happens is when two processes end up waiting on each other.

Basically a query takes a lock on table1. Another query takes a lock on table2, then tables a lock on table1. The second query is now blocked by the first query. The first query then tries to access table2. Both queries are now waiting on the other query so we have a deadlock. SQL picks a query to kill, then rolls that query back allowing the remaining query to complete.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[wbodger]

So, I looked at the tables and the query in question and added one more index. That took care of the problem, thanks!