August 17, 2010 at 6:24 am
There is some deadlock appeared in the database, can any one tell me how to come out of deadlock situations.
Rgards,
Naveen
August 17, 2010 at 6:30 am
Naveen..
This is a very open questin.... to prevent this happening again.. you need to have understanding of your app and then you need to understand why it happened...
Below are some good links which you can refer to start your investigation...
http://msdn.microsoft.com/en-us/library/ms178104.aspx
http://www.sqlservercentral.com/Forums/Topic514877-146-1.aspx#bm515674
and here you need to provide as much as info you've to get to the point answer... as there can be 100s of reason for this to happen.... so post all the info you've or can gather... and lets see what we can make out of that....:-)
Tell me if this helps....
Rohit
August 17, 2010 at 6:37 am
how to clear the deadlocks, is it required to restart the sql server services or is anyother method.
August 17, 2010 at 6:40 am
See deadlock is automatically handeled by sql server we don't need to do anything except do our investigation to find out the root cause and taking preventive measures so that it never happens again....
August 17, 2010 at 6:41 am
Here's a very crud way for solving the problem once in a while.
exec sp_who2
Look at the blocked by column. That will list the spid that is most likely blocking everyone else.
then run KILL <put spid here>.
Then rerun sp_who2 again to see if the lock is resolved. Sometimes you need to do this 2-3 times.
This stops the immediate problem but it doesn't prevent it from happenning again.
Also keep in mind that all the transations that were running need to be restarted.
August 17, 2010 at 6:41 am
SQL automatically resolves deadlocks as soon as it detects them. The error message that one gets is a notification that SQL detected the deadlock, resolved it by killing one session, and that session was yours.
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
August 17, 2010 at 6:42 am
Although u need to be very cautious doing this on a production box... and not at all recommended unless you're facing some serious perfomance issues and you need to be aware of the impact as well....
August 17, 2010 at 6:45 am
Ninja's_RGR'us (8/17/2010)
Here's a very crud way for solving the problem once in a while.exec sp_who2
Look at the blocked by column. That will list the spid that is most likely blocking everyone else.
then run KILL <put spid here>.
Then rerun sp_who2 again to see if the lock is resolved. Sometimes you need to do this 2-3 times.
For blocking. Not for deadlocks.
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
August 17, 2010 at 6:53 am
Hi,
you need find out, if any blocks occurred in your database
use master
select * from sysprocesses where open_tran<>0
if any uncommitted transactions occurred in your database. blocks are created. Check it and fix
it.
With Regards
Balaji.G
August 15, 2011 at 1:54 pm
Is this sql server 2005? Then run a profiler and capture the deadlock graph. Deadlock can occur because of a lot of reasons and the deadlock graph will give you a good idea where to start.
Also, just an observation. Most of the times while resolving deadlocks,we tend to look for X locks only. But please do examine the S locks also and let us just remember that a deadlock occurs on one (or more) resource. That resource can be a table itself, a row, a page, an index anything.
Even a simple select and then and update statement may lead to a deadlock.
So best idea would be to get hold of the deadlock graph first and then only can this problem be solved
August 16, 2011 at 1:12 am
Hello Folks,
am in similar kind of situation, on one of my Production server there are so many lockings are there it is causing performance problem.
1)I run a query sp_who4 active it results in same query with same login but different SPID's are showing in command text column.
2) SPID is blocking by same SPID is more than once.
i.e., ex: SPID:98 is BLKBy 98.
3) I run a SQL profiler and please guide me what to do next and how to fix this issue ASAP.
August 16, 2011 at 3:21 am
srinath.vanama (8/16/2011)
Hello Folks,am in similar kind of situation, on one of my Production server there are so many lockings are there it is causing performance problem.
1)I run a query sp_who4 active it results in same query with same login but different SPID's are showing in command text column.
2) SPID is blocking by same SPID is more than once.
i.e., ex: SPID:98 is BLKBy 98.
3) I run a SQL profiler and please guide me what to do next and how to fix this issue ASAP.
Srinath... This is a year old thread...
See when you're seeing a spid blocking itself then this is not a blocking issue this is a common scenario in multiple core systems....
Can you confirm you SQL Server edition and sp level.
August 16, 2011 at 3:32 am
Sql Server 2005 Enterprise edition and SP2.
If this is not a Blocking issue, what might be the problem and how to fix this issue.?
Kindly suggest me to fix this problem.
August 16, 2011 at 3:34 am
Medhatithi Guha (8/15/2011)
Is this sql server 2005? Then run a profiler and capture the deadlock graph. Deadlock can occur because of a lot of reasons and the deadlock graph will give you a good idea where to start.Also, just an observation. Most of the times while resolving deadlocks,we tend to look for X locks only. But please do examine the S locks also and let us just remember that a deadlock occurs on one (or more) resource. That resource can be a table itself, a row, a page, an index anything.
Even a simple select and then and update statement may lead to a deadlock.
So best idea would be to get hold of the deadlock graph first and then only can this problem be solved
Please start a new thread.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply