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...
>
>