SQL Server 2005 SP Deadlock issue

Question

I have a scheduled job with a SP running on daily basis (SQL Server 2005). Recently I frequently encounter deadlock problem for this SP. Here is the error message:

Message
Executed as user: dbo. Transaction (Process ID 56) was deadlocked on thread | 
communication buffer resources with another process and has been chosen as the deadlock 
victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205).  The step failed.

The SP uses some inter joined views to some tables, one of them is a large size data table with several million rows of data(and keep growing). I am not sure if any job or query against to the table will cause the SP un-accessible to the table? I am going to investigate who is on line by using the query. That may expose some query or person on SQL server during that time.

Not sure if any one have similar issue or this is known SQL 2005 issue? Any additional way I should do in my SP or on SQL server to avoid the deadlock?

Answer 1

Use the SQL Server Profiler to track all the queries that are running. I put the output into SQL Server. This will help you figure out which ones are accessing your particular table / tables. Post your findings, and we can help you with that.

Answer 2

Deadlocks are when two transactions are each holding onto some resources and want a resource that the other one has as well - neither can proceed as they are both waiting for each other. They cannot be completely eliminated, but a lot can be done to mitigate them. Remus and Raj suggest capturing more information about them in Profiler - which I also recommend - generally optimizing your queries (if you know which ones are involved) can also help. Here is an MSDN article that can help get you going: "Minimizing Deadlocks".