please tell me how to know when a deadlock occurs in sp

can i write a trigger which tells me when a deadlock occursDo it on the client
The error number is 1204 if I remember well.
"raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
> can i write a trigger which tells me when a deadlock occurs|||Raghu
when ever deadlock occurs it throughs an error no :1205
so you can have a mechanism to set something like this for further
investigation through trigger.
IF @.@.ERROR = 1205
begin
-- EITHER NOTIFY OR INSERT INTO A TEMP TABLE
--RUN PROCEDURE AGAIN TO DO THE WORK.
END
Regards
R.D
"raghu veer" wrote:

> can i write a trigger which tells me when a deadlock occurs|||Uri
1204 is used to trace TRACEFLAG(1204)
while error is 1205, I think
Regards
R.D
"Uri Dimant" wrote:

> Do it on the client
> The error number is 1204 if I remember well.
>
> "raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
> news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
>
>|||When a transaction is chosen as the deadlock victim, that transaction is
rolled back and a 1205 error is returned on the connection. The batch may
or may not be terminated, so you should add error handling code after every
statement in a transaction. If a rollback occurs within a trigger, that
trigger continues executing the balance of its body and then the batch is
terminated. This means that error handling is needed within the body of a
trigger as well. No additional triggers are fired and no statements
following the statement that caused the trigger to fire execute. Because
the batch may also be terminated by a 1205 error, you must add code to
detect it in the client app.
Error handling code must exist in both the stored procedure and the client
app. This is extremely important, because if you fail to check for errors
after every statement within a transaction, the statements following the
error will execute in autocommit mode, which can introduce inconsistency
into the database. In addition, if you have multiple data modification
statements within a trigger, you must also add error handling code after
each statement in the trigger. This cannot be stressed enough. Tracking
down the resultant data corruption is extremely difficult to do.
It is not possible to write a trigger that will detect all deadlocks and
inform you when they occur. The trigger containing the detection and
notification code may not fire if the deadlock occurs while another trigger
on the same table is executing.
"raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
> can i write a trigger which tells me when a deadlock occurs|||Raghu
Follow these steps in dev node.
1)DBCC TRACEON(3604)
2)DBCC TRACEON(1204)
3) RUN SPs that are raising deadlocks
4) you can analyse the results and find out which object is becoming dead
lock victim and even which line of sp is causing deadlock
5) attack query
If you have problem in analysing and finding the object Post results here
Regards
R.D
"Brian Selzer" wrote:
> When a transaction is chosen as the deadlock victim, that transaction is
> rolled back and a 1205 error is returned on the connection. The batch may
> or may not be terminated, so you should add error handling code after ever
y
> statement in a transaction. If a rollback occurs within a trigger, that
> trigger continues executing the balance of its body and then the batch is
> terminated. This means that error handling is needed within the body of a
> trigger as well. No additional triggers are fired and no statements
> following the statement that caused the trigger to fire execute. Because
> the batch may also be terminated by a 1205 error, you must add code to
> detect it in the client app.
> Error handling code must exist in both the stored procedure and the client
> app. This is extremely important, because if you fail to check for errors
> after every statement within a transaction, the statements following the
> error will execute in autocommit mode, which can introduce inconsistency
> into the database. In addition, if you have multiple data modification
> statements within a trigger, you must also add error handling code after
> each statement in the trigger. This cannot be stressed enough. Tracking
> down the resultant data corruption is extremely difficult to do.
> It is not possible to write a trigger that will detect all deadlocks and
> inform you when they occur. The trigger containing the detection and
> notification code may not fire if the deadlock occurs while another trigge
r
> on the same table is executing.
> "raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
> news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
>
>|||i executed both now there is no errror
tomorrow i will execute them and let u know
thankssssssssssssssssssssssssssss
"R.D" wrote:
> Raghu
> Follow these steps in dev node.
> 1)DBCC TRACEON(3604)
> 2)DBCC TRACEON(1204)
> 3) RUN SPs that are raising deadlocks
> 4) you can analyse the results and find out which object is becoming dead
> lock victim and even which line of sp is causing deadlock
> 5) attack query
> If you have problem in analysing and finding the object Post results here
> Regards
> R.D
>
> "Brian Selzer" wrote: