SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Need Help.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

151 Posts

Posted - 04/12/2013 :  01:22:52  Show Profile  Reply with Quote
Hi Guys,

I need help.

I have few questions.

1) Let say if i am running any query
E.G select * from table1 and its taking to long to execute it and i stop the query
did this step cause table dead lock problem?

2) when you execute sp_who and if status = runnable, what do you think, did this
causing table dead lock error.

I know how dead lock happend.

3) Is there any query to find out which table has dead lock?

Any help would be great appreciate.

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/12/2013 :  01:54:53  Show Profile  Reply with Quote
By using SQL Profiler, you can get to know the details
http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/

http://www.intertech.com/Blog/Post/SQL-Server-Deadlocks-Caused-By-Clustered-Index-Scan.aspx

Edited by - bandi on 04/12/2013 01:57:07
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/12/2013 :  10:12:01  Show Profile  Reply with Quote
assuming that you know about what is deadlock or how it occurs.

by default, when a deadlock occurs, SQL server kills (and rollback) one of the process automatically with a message. And the other one is executed.

So to your questions

1) If your query took considerably longer than normal execution time and you stop it explicitly, then most likely it would be a locked situation instead of a deadlock. since as I mentioned deadlocked situation is handled by SQL server automatically.

2) I would rather like you to refer towards BOL to find out the exact meaning of each column's value. In your case, blkBy information will tell you whether the process is, blocking/blocked by some, other process.

3) In addition to the @bandi's two references, you may like to have go through the extended events, reading SQL server error log files.

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000