SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


deadlock


deadlock

Author
Message
MasterDB
MasterDB
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 574
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.
Ramji29
Ramji29
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 479
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.
MasterDB
MasterDB
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 574
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30399 Visits: 9671
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search