rocky_498 (1/7/2013)
The Store Procedure "MyStoreProcedure" has 19 Pages hard to post here ( I hope you understand). Some time this SP work fine and Some time i am getting Dead Lock Error.....
Then I suggest you start by rewriting that procedure and simplifying it (or splitting it up). A several page stored proc is a good indication that there's a design problem.
Once you've simplified and broken the procedure down, if you're still having deadlocks post the new deadlock graph.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability