Posted by Erland Sommarskog on 11/29/06 22:46

laststubborn (arafatsalih@gmail.com) writes:
> Sorry for the late respond. Eventhough we solved the problem and the
> problem was coming from the application, I would like to know my
> necessary steps to take the action on SQL Server along with your
> suggestions.
>
> Here are the answers of your questions:
> -Yes the same SP was kept locing the Database
> -The other processes are not the same we have other SELECt or UPDATE or
> INSERT processes on our Database
> -Our database is MS SQL 20000

I'm afraid that I don't have much to add than teh suggestion to use
aba_lockinfo to get an overview of who is locking whom, and from this
try to understand why.

One situation that I should have mentioned is that if your application
has set up a command timeout (which is 30 seconds by default in many
APIs) and cancels the batch after this time, the application should
always submit a

IF @@trancount > 0 ROLLBACK TRANSACTION

since a timeout expired does not rollback any transactions, and not rolling
back in this situations can lead to locks piling up.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]