Sourav,

I tried testing your script by doing the following:

- Create one database connection and run the following code:

IF (SELECT object_id ('tempdb..##tmp1','U')) IS NOT NULL

DROP TABLE ##tmp1

CREATE TABLE ##tmp1 (

col1 INT

)

INSERT ##tmp1 (col1) VALUES (0), (1), (2), (3), (4), (5)

GO

BEGIN TRAN

UPDATE ##tmp1 SET col1 = col1 + 10

- Create a second database connection and run the following code:

IF (SELECT object_id ('tempdb..##tmp2','U')) IS NOT NULL

DROP TABLE ##tmp2

CREATE TABLE ##tmp2 (

col1 INT

)

INSERT ##tmp2 (col1) VALUES (0), (1), (2), (3), (4), (5)

GO

BEGIN TRAN

UPDATE ##tmp2 SET col1 = col1 + 10

- Go to the first connection and run the following code:

UPDATE ##tmp2 SET col1 = col1 + 20

- Go to the second connection and run the following code:

UPDATE ##tmp1 SET col1 = col1 + 20

- I got the following message on the second query:

Msg 1205, Level 13, State 45, Line 1

Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I then ran your code and got a Total_DeadLock_Count of 0. I also looked in the error log and there aren't any entries with '%deadlock%' in the text.

Do you have some other process that writes 'Deadlock encountered .... Printing deadlock information' to the error log?

Thanks,

Mike