Solved

SQL Server 2008 Deadlock

Posted on 2014-01-15
5
741 Views
Last Modified: 2014-01-19
How to identify dead lock in SQL 2008 ? How do I know whether a dead lock occur in production environment ? Will the SQL server slow down the performance ?
0
Comment
Question by:AXISHK
  • 2
  • 2
5 Comments
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 200 total points
Comment Utility
Try running the SP_WHO and SP_WHO2 stored procedures. They will show you blocked and blocked by columns that correspond with a session ID. Look up the session ID to see who is locking the database.
0
 
LVL 1

Expert Comment

by:mike706
Comment Utility
You can also use the activity monitor to see this information.

You can find more information about deadlocks here http://technet.microsoft.com/en-us/library/ms177433%28v=sql.105%29.aspx

Deadlocks are a performance problem because they can prevent 2 or more processes from being able to process data.  A deadlock chain can occur and impact hundreds of spids based on the data access patterns, number of users, object dependencies, etc.
0
 

Author Comment

by:AXISHK
Comment Utility
Run 'sp_who" and 'sp_who2' but I can't find the 'blocked" or 'blocked  by" column.  Do I make something wrong ?

Tks
0
 
LVL 1

Accepted Solution

by:
mike706 earned 300 total points
Comment Utility
It should look something like this.
sp_who2
sp-who2.jpg
0
 

Author Closing Comment

by:AXISHK
Comment Utility
Tks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now