question

Bhupendra99 avatar image
Bhupendra99 asked

Capture deadlock

Please follow below mention steps to create a dedlock Create Table first Create table Authors ( id int, name varchar(300), nou varchar(100), ekaur int ) execute it in query window 2 DECLARE @au_id varchar(11), @au_lname varchar(40) SELECT @au_id = '111-11-1112', @au_lname = 'test2' declare @error varchar(max) BEGIN TRANSACTION BEGIN TRY INSERT Authors VALUES (@au_id, @au_lname, '11111', 0) WAITFOR DELAY '00:00:05' SELECT COUNT(*) FROM Authors COMMIT END TRY BEGIN CATCH SELECT @error=ERROR_NUMBER() Insert into mesage select @error ROLLBACK END CATCH; SELECT @@TRANCOUNT AS '@@Trancount' execute it in query window 1 DECLARE @au_id varchar(11), @au_lname varchar(40) SELECT @au_id = '111-11-1111', @au_lname = 'test1' declare @error varchar(max) BEGIN TRANSACTION BEGIN TRY INSERT Authors VALUES (@au_id, @au_lname, '11111', 0) WAITFOR DELAY '00:00:05' SELECT COUNT(*) FROM Authors COMMIT END TRY BEGIN CATCH SELECT @error=ERROR_NUMBER() Insert into mesage select @error ROLLBACK END CATCH; SELECT @@TRANCOUNT AS '@@Trancount' -------------------- I want to store deadlock messages in a Table but when I execute the above mentioned steps i got messeage as Msg 3930, Level 16, State 1, Line 16 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. if I remove the Insert into message part it works fine can anybody tell me how can I store the deadlock messages Please note that I cannot turn the trace on for storing deadlock info
deadlock
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. You can mark your own answer too.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Deadlocks are captured multiple ways within SQL Server. If you're running SQL Server 2008 or better, you can use the [system_health extended events session][1]. It's not something you have to turn on. It's already running and it's capturing deadlock graphs for you in real time. If you're on an older system you can enable traceflag 1222 to capture deadlock information to the error log. Also, as you noted, but don't want to use, you can use trace events on older systems. I wouldn't suggest that either. [1]: http://msdn.microsoft.com/en-us/library/ff877955.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bhupendra99 avatar image
Bhupendra99 answered
I found WAY OF MAINTAINING DEADLOCK i JUST HAVE TO WRITE Insert into mesage select @error after the roolback
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.