April 11, 2008 at 1:56 am
Hello All,
I'm facing a deadlock situation in one of our production server running on SQL 2K and initaly I got the error in my application log. and the application log information is as follows.
------------------------------
04/10/08 05:25:04 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:04 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('76232801','76232801',4,'04-10-2008 05:16:33')
04/10/08 05:25:09 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:09 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040063','29040063',4,'04-10-2008 05:16:35')
04/10/08 05:25:16 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:16 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040063','29040063',8,'04-10-2008 05:16:35')
04/10/08 05:25:21 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:21 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('','76232801',3,'04-10-2008 05:16:41')
04/10/08 05:25:26 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:26 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040068','29040068',4,'04-10-2008 05:16:46')
04/10/08 05:25:31 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:31 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040068','29040068',8,'04-10-2008 05:16:46')
04/10/08 05:25:36 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:36 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040073','29040073',4,'04-10-2008 05:17:03')
04/10/08 05:25:41 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:41 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040073','29040073',8,'04-10-2008 05:17:03')
04/10/08 05:25:43 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:43 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:43 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040081','29040081',4,'04-10-2008 05:17:11')
04/10/08 05:25:43 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040081','29040081',8,'04-10-2008 05:17:11')
04/10/08 05:25:55 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:55 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040086','29040086',4,'04-10-2008 05:17:19')
04/10/08 05:26:05 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:26:05 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040086','29040086',8,'04-10-2008 05:17:19')
04/10/08 05:26:07 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:26:07 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR - FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:26:07 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040089','29040089',4,'04-10-2008 05:17:25')
04/10/08 05:26:07 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR - Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values ('29040089','29040089',8,'04-10-2008 05:17:26')
------------------------------
After I got this error I ran the below trace...
DBCC TRACEON (3605,1204,-1)
And I didn't got the deadlock Information in my error Log......Below are few entries for the same time and date from sql error log.
--------------------------------
04/10/2008 05:27:28,spid4,Unknown,Starting deadlock search 338817
04/10/2008 05:27:28,spid4,Unknown,Target Resource Owner:
04/10/2008 05:27:28,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:104 ECID:0 Ec:(0x3E1BF548) Value:0x42c3e2e4
04/10/2008 05:27:28,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:104 ECID:0 Ec:(0x3E1BF548) Value:0x42c3e2e4
04/10/2008 05:27:28,spid4,Unknown,
04/10/2008 05:27:28,spid4,Unknown,End deadlock search 338817 ... a deadlock was not found.
04/10/2008 05:27:28,spid4,Unknown,----------------------------------
04/10/2008 05:26:53,spid4,Unknown,----------------------------------
04/10/2008 05:26:53,spid4,Unknown,Starting deadlock search 338815
04/10/2008 05:26:53,spid4,Unknown,Target Resource Owner:
04/10/2008 05:26:53,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:51 ECID:0 Ec:(0x4FA73538) Value:0x42c3e900
04/10/2008 05:26:53,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:51 ECID:0 Ec:(0x4FA73538) Value:0x42c3e900
04/10/2008 05:26:53,spid4,Unknown,
04/10/2008 05:26:53,spid4,Unknown,End deadlock search 338815 ... a deadlock was not found.
04/10/2008 05:26:53,spid4,Unknown,----------------------------------
04/10/2008 05:26:53,spid4,Unknown,----------------------------------
04/10/2008 05:26:53,spid4,Unknown,Starting deadlock search 338816
04/10/2008 05:26:53,spid4,Unknown,Target Resource Owner:
04/10/2008 05:26:53,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:51 ECID:1 Ec:(0x5CDA80C0) Value:0x42c3e9b8
04/10/2008 05:26:53,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:51 ECID:1 Ec:(0x5CDA80C0) Value:0x42c3e9b8
04/10/2008 05:26:53,spid4,Unknown,
04/10/2008 05:26:53,spid4,Unknown,End deadlock search 338816 ... a deadlock was not found.
04/10/2008 05:26:53,spid4,Unknown,----------------------------------
04/10/2008 05:25:33,spid4,Unknown,----------------------------------
04/10/2008 05:25:33,spid4,Unknown,Starting deadlock search 338813
04/10/2008 05:25:33,spid4,Unknown,Target Resource Owner:
04/10/2008 05:25:33,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:68 ECID:0 Ec:(0x3BC0F538) Value:0x42c3fe90
04/10/2008 05:25:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:0 Ec:(0x3BC0F538) Value:0x42c3fe90
04/10/2008 05:25:33,spid4,Unknown,Node:2 ResType:ExchangeId Stype:'AND' SPID:68 ECID:1 Ec:(0x5A4760C0) Value:0x42c3f030
04/10/2008 05:25:33,spid4,Unknown,
04/10/2008 05:25:33,spid4,Unknown,End deadlock search 338813 ... a deadlock was not found.
04/10/2008 05:25:33,spid4,Unknown,----------------------------------
04/10/2008 05:25:33,spid4,Unknown,----------------------------------
04/10/2008 05:25:33,spid4,Unknown,Starting deadlock search 338814
04/10/2008 05:25:33,spid4,Unknown,Target Resource Owner:
04/10/2008 05:25:33,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:68 ECID:1 Ec:(0x5A4760C0) Value:0x42c3f030
04/10/2008 05:25:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:1 Ec:(0x5A4760C0) Value:0x42c3f030
04/10/2008 05:25:33,spid4,Unknown,
04/10/2008 05:25:33,spid4,Unknown,End deadlock search 338814 ... a deadlock was not found.
04/10/2008 05:25:33,spid4,Unknown,----------------------------------
04/10/2008 05:25:03,spid4,Unknown,----------------------------------
04/10/2008 05:25:03,spid4,Unknown,Starting deadlock search 338811
04/10/2008 05:25:03,spid4,Unknown,Target Resource Owner:
04/10/2008 05:25:03,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:68 ECID:25 Ec:(0x49F3C0C0) Value:0x42c3e118
04/10/2008 05:25:03,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:25 Ec:(0x49F3C0C0) Value:0x42c3e118
04/10/2008 05:25:03,spid4,Unknown,
04/10/2008 05:25:03,spid4,Unknown,End deadlock search 338811 ... a deadlock was not found.
04/10/2008 05:25:03,spid4,Unknown,----------------------------------
04/10/2008 05:25:03,spid4,Unknown,----------------------------------
04/10/2008 05:25:03,spid4,Unknown,Starting deadlock search 338812
04/10/2008 05:25:03,spid4,Unknown,Target Resource Owner:
04/10/2008 05:25:03,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:68 ECID:24 Ec:(0x6B76C0C0) Value:0x42c3fcc4
04/10/2008 05:25:03,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:24 Ec:(0x6B76C0C0) Value:0x42c3fcc4
04/10/2008 05:25:03,spid4,Unknown,
04/10/2008 05:25:03,spid4,Unknown,End deadlock search 338812 ... a deadlock was not found.
04/10/2008 05:25:03,spid4,Unknown,----------------------------------
04/10/2008 05:24:48,spid4,Unknown,----------------------------------
04/10/2008 05:24:48,spid4,Unknown,Starting deadlock search 338810
04/10/2008 05:24:48,spid4,Unknown,Target Resource Owner:
04/10/2008 05:24:48,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:68 ECID:0 Ec:(0x3BC0F538) Value:0x42c3f9e4
04/10/2008 05:24:48,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:0 Ec:(0x3BC0F538) Value:0x42c3f9e4
04/10/2008 05:24:48,spid4,Unknown,
04/10/2008 05:24:48,spid4,Unknown,End deadlock search 338810 ... a deadlock was not found.
04/10/2008 05:24:48,spid4,Unknown,----------------------------------
04/10/2008 05:24:33,spid4,Unknown,----------------------------------
04/10/2008 05:24:33,spid4,Unknown,Starting deadlock search 338800
04/10/2008 05:24:33,spid4,Unknown,Target Resource Owner:
04/10/2008 05:24:33,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:51 ECID:0 Ec:(0x66667538) Value:0x42c3f988
04/10/2008 05:24:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:51 ECID:0 Ec:(0x66667538) Value:0x42c3f988
04/10/2008 05:24:33,spid4,Unknown,
04/10/2008 05:24:33,spid4,Unknown,End deadlock search 338800 ... a deadlock was not found.
04/10/2008 05:24:33,spid4,Unknown,----------------------------------
04/10/2008 05:24:33,spid4,Unknown,----------------------------------
04/10/2008 05:24:33,spid4,Unknown,Starting deadlock search 338801
04/10/2008 05:24:33,spid4,Unknown,Target Resource Owner:
04/10/2008 05:24:33,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:51 ECID:5 Ec:(0x553DC0C0) Value:0x42c3fd20
04/10/2008 05:24:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:51 ECID:5 Ec:(0x553DC0C0) Value:0x42c3fd20
04/10/2008 05:24:33,spid4,Unknown,
04/10/2008 05:24:33,spid4,Unknown,End deadlock search 338801 ... a deadlock was not found.
--------------------------------
Can anybody help in in this.......what should I do?
Rohit
April 11, 2008 at 2:05 am
Now that you've enabled those trace flags, the next time a deadlock occurs you'll get some more info in the error log about the deadlock. The fact that you've seen nothing yet is just because it hasn't happened since you enabled the trace flags. You're just going to have to sit it out and wait.
April 11, 2008 at 2:14 am
what is the isolation level your application is using? you saw these deadlocks only this time, or are you seeing frequently? do you normally do index maintenances?
April 11, 2008 at 4:04 am
Thx Guys for Replying my post....
Karl: If you compare the date & time in both the logs then u will find that as per application their is a occurance of deadlock but its not showing any entry for the same in sql server log.
FYI: both the logs entries are after running the trace.
I never changed the isolation level, and It should be the default 'Read Commited' but how can I know the current isloation level?
Rohit
April 11, 2008 at 4:18 am
cooldba (4/11/2008)
Thx Guys for Replying my post....Karl: If you compare the date & time in both the logs then u will find that as per application their is a occurance of deadlock but its not showing any entry for the same in sql server log.
FYI: both the logs entries are after running the trace.
I never changed the isolation level, and It should be the default 'Read Commited' but how can I know the current isloation level?
Rohit
Ah OK. I thought you'd said that you enabled the trace flags after you got the error. Strange thing is that the first set of errors include "(Pacific Daylight Time)" next to the time.
Incidentally, I don't think you need trace flag 3605 enabled. But you could do with having trace flag 1205 enabled as well.
One they're both enabled try and create a deadlock situation manually by writing a couple of scripts. Then check the error log to see if it recorded the information in the error log.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply