Deadlock detection problem! help

Hello faculties,

i'm encountering a strange a deadlock problem on my remote server
which is used to give application demo to the client. It has happened
that on one of the databases a deadlock situation is taking place.

What is the most detailed way to detect such the cause of such a
deadlock to the innermost level of detail, like what statements, stored
procedures and locks are causing the deadlock to occur.

Guys! please help me out!

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***debian mojo (debian_mojo@.yahoo.com) writes:
> i'm encountering a strange a deadlock problem on my remote server
> which is used to give application demo to the client. It has happened
> that on one of the databases a deadlock situation is taking place.
> What is the most detailed way to detect such the cause of such a
> deadlock to the innermost level of detail, like what statements, stored
> procedures and locks are causing the deadlock to occur.

DBCC TRACEON (1204, 1)
DBCC TRACEON (3605, 1)

It's common to add these as start up parameters (best done from Enterprise
Manager).

This gives you a deadlock trace to the SQL Server log. Unfortunately,
though, it's a bit cryptic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?
Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com

Deadlock detection

My app uses ADO 2.8 with C++.
Is there any way to retrieve sufficient information when a deadlock is
occurs between two ADO connections to an SQL server? It will be a great help
if I could get the last queries executed and current lock rangies of two
connections soon as a deadlock occurs.
Of course, I know there is no perfect deadlock-free database applcation.
However, my case is serious, because there are too many deadlocks and I
cannot still figure out the cause.
Please reply. Any replies are appreciated.
Thanks in advance.
Regards,
Hyun-jik BaeYou can investigate trace flag 1204. Search for 1204 in Books Online and Kno
wledgebase for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
news:OjlpkEkRFHA.252@.TK2MSFTNGP12.phx.gbl...
> My app uses ADO 2.8 with C++.
> Is there any way to retrieve sufficient information when a deadlock is occ
urs between two ADO
> connections to an SQL server? It will be a great help if I could get the l
ast queries executed and
> current lock rangies of two connections soon as a deadlock occurs.
> Of course, I know there is no perfect deadlock-free database applcation. H
owever, my case is
> serious, because there are too many deadlocks and I cannot still figure ou
t the cause.
> Please reply. Any replies are appreciated.
> Thanks in advance.
> Regards,
> Hyun-jik Bae
>|||Thanks! I will check it out right now.
Regards,
Hyun-jik Bae
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ua1J1KkRFHA.576@.TK2MSFTNGP15.phx.gbl...
> You can investigate trace flag 1204. Search for 1204 in Books Online and
> Knowledgebase for more information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
> news:OjlpkEkRFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||If you want the last query executed on a connection (PSID) you can use the
DBCC INPUTBUFFER. Not the most reliable because the buffer can change very
quickly.
You have more several options:
The simplest way to get the last query is to trace and log using the SQL
Profiler. Setup the profiler to capture:
TSQL Event
SQL:BatchComplete
Stored Procedures Event Category
RPC:Complete.
If using distributed transactions you may want to add
Transactions Event Category
Transactions:DTCTransactions
Transactions:TransactionLog
Check the BOL (SQL Books On Line) to make sure the correct data columns are
being logged.
Another way is to set up server side logging. This setup using the family of
sp_trace_XXXX stored procedures. If I remember correctly SQL profiler can
create a these server side scripts. Only the log file paths and NTFS
permissions need to be set.
If you have difficulty reproducing the blocking problem you can follow the
directions in the knowledge base article Q271509 "How to monitor SQL Server
2000 blocking"
http://support.microsoft.com/defaul...kb;en-us;224453 and Q224453
"INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems"
http://support.microsoft.com/defaul...kb;en-us;224453
The majority of reoccurring blocking problems is a symptom of a poor
relation database design. Typically the tables are not following 3NF rules
and are in some abnormal form. This is my recommended place to start.
"Bae,Hyun-jik" wrote:

> My app uses ADO 2.8 with C++.
> Is there any way to retrieve sufficient information when a deadlock is
> occurs between two ADO connections to an SQL server? It will be a great he
lp
> if I could get the last queries executed and current lock rangies of two
> connections soon as a deadlock occurs.
> Of course, I know there is no perfect deadlock-free database applcation.
> However, my case is serious, because there are too many deadlocks and I
> cannot still figure out the cause.
> Please reply. Any replies are appreciated.
> Thanks in advance.
> Regards,
> Hyun-jik Bae
>
>|||See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Bae,Hyun-jik" wrote:

> Thanks! I will check it out right now.
> Regards,
> Hyun-jik Bae
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:ua1J1KkRFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
--
David Gugick
Imceda Software
www.imceda.com