Sunday, January 17, 2010

Finding out which sps are in deadlock in SQL server

When my current Silverlight 3 project went into QA environment, I got one more chance to met my old friend.Yes It’s deadlock.But unfortunately we were not able to reproduce it in the dev environment.There started the problem.
But after a big battle in my dev machine I was able to reproduce the deadlock.Steps were simple.I just need to do the normal operations in the application at lightening speed.
Then we knew its a problem with the WCF service calls from the client to server.There were 2 calls going from client to server while saving where it is supposed to be one by according to the design of the application.But application is in QA.So we tried to resolve that by introducing some mechanisms in the DB.To continue that we needed the information about the SPs which are in deadlock.
Since our application implements the transaction from code and that transaction contains so many sp calls one by one it was very difficult to figure out which sp is in trouble.If we try debug mode there won’t be any deadlock.Finally our DBA given me 2 methods to find out the sps.
The below one give the details about the things happening inside the sql server including the SPID.

sp_who2

Execute the above command in SQL Server Management Studio on your DB.Once you get the SPID use that to find out exactly what is deadlocked in the below method.

dbcc inputbuffer (<SPID>)
I know for a DBA this is not a big thing.But for a developer I think this will help him a lot.

1 comment:

Blogger said...

Did you know that that you can earn cash by locking premium pages of your blog or site?
Simply join Mgcash and embed their content locking plug-in.