<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
deadlock
14 posts, Page 1 of 2
1
2
»»
deadlock
Rate Topic
Display Mode
Topic Options
Author
Message
ekknaveen
ekknaveen
Posted Tuesday, August 17, 2010 6:24 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, November 22, 2012 2:43 AM
Points: 177,
Visits: 268
There is some deadlock appeared in the database, can any one tell me how to come out of deadlock situations.
Rgards,
Naveen
Post #970325
Ramji29
Ramji29
Posted Tuesday, August 17, 2010 6:30 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, August 31, 2012 7:19 AM
Points: 225,
Visits: 419
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
Post #970331
ekknaveen
ekknaveen
Posted Tuesday, August 17, 2010 6:37 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, November 22, 2012 2:43 AM
Points: 177,
Visits: 268
how to clear the deadlocks, is it required to restart the sql server services or is anyother method.
Post #970342
Ramji29
Ramji29
Posted Tuesday, August 17, 2010 6:40 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, August 31, 2012 7:19 AM
Points: 225,
Visits: 419
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....
Post #970350
Ninja's_RGR'us
Ninja's_RGR'us
Posted Tuesday, August 17, 2010 6:41 AM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 21,357,
Visits: 9,537
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.
Post #970351
GilaMonster
GilaMonster
Posted Tuesday, August 17, 2010 6:41 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 37,726,
Visits: 29,987
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #970354
Ramji29
Ramji29
Posted Tuesday, August 17, 2010 6:42 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, August 31, 2012 7:19 AM
Points: 225,
Visits: 419
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....
Post #970356
GilaMonster
GilaMonster
Posted Tuesday, August 17, 2010 6:45 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 37,726,
Visits: 29,987
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #970362
balaji.ganga
balaji.ganga
Posted Tuesday, August 17, 2010 6:53 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, February 03, 2011 3:00 AM
Points: 164,
Visits: 656
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
Post #970372
Medhatithi Guha
Medhatithi Guha
Posted Monday, August 15, 2011 1:54 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, June 27, 2012 1:03 PM
Points: 4,
Visits: 40
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
Post #1160221
« Prev Topic
|
Next Topic »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
