Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Number of Deadlocks/Sec on database level alert

Author  Topic 

cottage125
Starting Member

32 Posts
Posted - 2008-12-24 : 12:50:53
Hi We have an alert in our Production SQL 2000(8.0.760) Cluster Server for
number of deadlocks/Sec on page raises above 1 then create alert AND
number of deadlocks/Sec on database raises above 1 then create alert
yesterday we have some maintenance and we reboot this production server
(no changes on this productioin server) as soon as we reboot I was getting this messages every 20 seconds
no one was connected right after reboot
(we had this issues in past as well on page level number of deadlocks/sec >1)

DATE/TIME: 12/24/2008 9:42:50 AM

DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec' (instance 'Database') of object 'SQLServer:Locks' is now above the threshold of 1.00 (the current value is 2.00).

COMMENT: (None)

JOB RUN: (None)

Then I put traces on by DBCC TraceON(1204,-1)
DBCC TraceON(3605,-1)
and selected dbcc tracestatus(-1)
I can see these traces activated
I did EXEC sp_altermessage 1205, with_log, true as well
also i put some other traces like 3604 and 1205 globally
but when I checked error log I cant see anything there.
what i can see in error log like this

12/24/2008 07:01:40,spid4,Unknown,Starting deadlock search 275
12/24/2008 07:01:40,spid4,Unknown,Target Resource Owner:
12/24/2008 07:01:40,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:57 ECID:3 Ec:(0x5F540098) Value:0x800f3c0c
12/24/2008 07:01:40,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:57 ECID:3 Ec:(0x5F540098) Value:0x800f3c0c
12/24/2008 07:01:40,spid4,Unknown,
12/24/2008 07:01:40,spid4,Unknown,End deadlock search 275 ... a deadlock was not found.
12/24/2008 07:01:40,spid4,Unknown,----------------------------------
12/24/2008 07:01:40,spid4,Unknown,----------------------------------
12/24/2008 07:01:40,spid4,Unknown,Starting deadlock search 276
12/24/2008 07:01:40,spid4,Unknown,Target Resource Owner:
12/24/2008 07:01:40,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:57 ECID:2 Ec:(0x5F548098) Value:0x800f3bb0
12/24/2008 07:01:40,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:57 ECID:2 Ec:(0x5F548098) Value:0x800f3bb0
12/24/2008 07:01:40,spid4,Unknown,
12/24/2008 07:01:40,spid4,Unknown,End deadlock search 276 ... a deadlock was not found.
12/24/2008 07:01:40,spid4,Unknown,----------------------------------
12/24/2008 07:01:40,spid4,Unknown,----------------------------------
12/24/2008 07:01:40,spid4,Unknown,Starting deadlock search 277
12/24/2008 07:01:40,spid4,Unknown,Target Resource Owner:
12/24/2008 07:01:40,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:57 ECID:1 Ec:(0x5F544098) Value:0x800f3b54
12/24/2008 07:01:40,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:57 ECID:1 Ec:(0x5F544098) Value:0x800f3b54
12/24/2008 07:01:40,spid4,Unknown,
12/24/2008 07:01:40,spid4,Unknown,End deadlock search 277 ... a deadlock was not found.
12/24/2008 07:01:40,spid4,Unknown,----------------------------------
12/24/2008 07:01:35,spid4,Unknown,
12/24/2008 07:01:35,spid4,Unknown,End deadlock search 270 ... a deadlock was not found.
12/24/2008 07:01:35,spid4,Unknown,----------------------------------
12/24/2008 07:01:35,spid4,Unknown,----------------------------------
12/24/2008 07:01:35,spid4,Unknown,Starting deadlock search 271
12/24/2008 07:01:35,spid4,Unknown,Target Resource Owner:
12/24/2008 07:01:35,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:57 ECID:3 Ec:(0x5F540098) Value:0x800f3c0c
12/24/2008 07:01:35,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:57 ECID:3 Ec:(0x5F540098) Value:0x800f3c0c
12/24/2008 07:01:35,spid4,Unknown,
12/24/2008 07:01:35,spid4,Unknown,End deadlock search 271 ... a deadlock was not found.
12/24/2008 07:01:35,spid4,Unknown,----------------------------------
12/24/2008 07:01:35,spid4,Unknown,----------------------------------
12/24/2008 07:01:35,spid4,Unknown,Starting deadlock search 272
12/24/2008 07:01:35,spid4,Unknown,Target Resource Owner:
12/24/2008 07:01:35,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:57 ECID:2 Ec:(0x5F548098) Value:0x800f3bb0
12/24/2008 07:01:35,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:57 ECID:2 Ec:(0x5F548098) Value:0x800f3bb0
12/24/2008 07:01:35,spid4,Unknown,
12/24/2008 07:01:35,spid4,Unknown,End deadlock search 272 ... a deadlock was not found.
12/24/2008 07:01:35,spid4,Unknown,----------------------------------
12/24/2008 07:01:35,spid4,Unknown,----------------------------------
12/24/2008 07:01:35,spid4,Unknown,----------------------------------
12/24/2008 07:01:35,spid4,Unknown,Starting deadlock search 270
12/24/2008 07:01:35,spid4,Unknown,Target Resource Owner:
12/24/2008 07:01:35,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:57 ECID:4 Ec:(0x5F54C098) Value:0x800f3c68
12/24/2008 07:01:35,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:57 ECID:4 Ec:(0x5F54C098) Value:0x800f3c68
12/24/2008 07:01:35,spid4,Unknown,Starting deadlock search 273
12/24/2008 07:01:35,spid4,Unknown,Target Resource Owner:
12/24/2008 07:01:35,spid4,Unknown,ResType:ExchangeId Stype:'AND' SPID:57 ECID:1 Ec:(0x5F544098) Value:0x800f3b54
12/24/2008 07:01:35,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:57 ECID:1 Ec:(0x5F544098) Value:0x800f3b54
12/24/2008 07:01:35,spid4,Unknown,
12/24/2008 07:01:35,spid4,Unknown,End deadlock search 273 ... a deadlock was not found.

Then I ran profiler for few seconds on my prod server and I have selected followinf events
in sql 2000
• Locks: Deadlock graph
• Locks: Lock: Deadlock
• Locks: Lock: Deadlock Chain
• Stored Proceddures:RPC:Completed
• Stored Proceddures:SP:StmtCompleted
• TSQL:SQL:BatchCompleted
• TSQL:SQL:BatchStarting

but i cant see any event with DEadlock
i was able to see some batchstarting and batchcomopleted events which are executing
some procs starts with 'QS_SS0E....'
i found those are from monitorng tool Quest and I disbaled monitoring after that but still
i am getting emails
then i went to perfmon to check and i have selected this number of deadlocks/sec on database level
for my prod server and I cant see this value >0

these 3 ways says its not deadlock i guess
then wht is it?? how can i stop this messages permenantly??
right now i put threshold tp 10 and i m not getting messages but i want final solution
appreciate ur help in this issue
Thanks
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site