December 23, 2008 at 11:04 am
we have alert in our prod server for SQL:Locks- Number of Deadlocks/Sec(page-level)
exceeds threshold of 1 then generate emails.
I was getting emails n I put 1204 and 3604 traces
I was able to see this deadlock information
Date,Source,Severity,Message
12/19/2008 14:36:17,spid3,Unknown,SQL Server is terminating due to 'stop' request from Service Control Manager.
12/19/2008 14:36:16,spid4,Unknown,Grant List 0::
12/19/2008 14:36:16,spid4,Unknown,Input Buf: Language Event: exec dbo.QS_SoSSE25_VirtualFileStats
12/19/2008 14:36:16,spid4,Unknown,Owner:0x90f3de80 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:3 ECID:0
12/19/2008 14:36:16,spid4,Unknown,SPID: 3 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 0
12/19/2008 14:36:16,spid4,Unknown,Requested By:
12/19/2008 14:36:16,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x49BD9A00) Value:0x90f3d820 Cost:(1/2710)
12/19/2008 14:36:16,spid4,Unknown,
12/19/2008 14:36:16,spid4,Unknown,Node:2
12/19/2008 14:36:16,spid4,Unknown,KEY: 1:30:1 (ee02c9935257) CleanCnt:1 Mode: U Flags: 0x0
12/19/2008 14:36:16,spid4,Unknown,Grant List 2::
12/19/2008 14:36:16,spid4,Unknown,Owner:0x90f3d200 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:63 ECID:0
12/19/2008 14:36:16,spid4,Unknown,SPID: 63 ECID: 0 Statement Type: INSERT Line #: 47
12/19/2008 14:36:16,spid4,Unknown,Requested By:
12/19/2008 14:36:16,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: X SPID:3 ECID:0 Ec:(0x801F03C0) Value:0x7623ba00 Cost:(0/44)
12/19/2008 14:36:16,spid4,Unknown,Victim Resource Owner:
12/19/2008 14:36:16,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x49BD9A00) Value:0x90f3d820 Cost:(1/2710)
12/19/2008 14:36:16,spid4,Unknown,
12/19/2008 14:36:16,spid4,Unknown,Wait-for graph
12/19/2008 14:36:16,spid4,Unknown,
12/19/2008 14:36:16,spid4,Unknown,Node:1
12/19/2008 14:36:16,spid4,Unknown,DB: 25 CleanCnt:1 Mode: X Flags: 0x0
12/19/2008 14:36:16,spid4,Unknown,Grant List 0::
12/19/2008 14:36:16,spid4,Unknown,Owner:0x90f3de80 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:3 ECID:0
12/19/2008 14:36:16,spid4,Unknown,SPID: 3 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 0
12/19/2008 14:36:16,spid4,Unknown,Requested By:
12/19/2008 14:36:16,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x49BD9A00) Value:0x90f3d820 Cost:(1/2710)
12/19/2008 14:36:16,spid4,Unknown,
12/19/2008 14:36:16,spid4,Unknown,Node:2
12/19/2008 14:36:16,spid4,Unknown,KEY: 1:30:1 (ee02c9935257) CleanCnt:1 Mode: U Flags: 0x0 (dbid=1,object-id- sysdatabases, index-id-1)
12/19/2008 14:36:16,spid4,Unknown,Grant List 2::
12/19/2008 14:36:16,spid4,Unknown,Owner:0x90f3d200 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:63 ECID:0
12/19/2008 14:36:16,spid4,Unknown,SPID: 63 ECID: 0 Statement Type: INSERT Line #: 47
12/19/2008 14:36:16,spid4,Unknown,Input Buf: Language Event: exec dbo.QS_SoSSE25_VirtualFileStats
12/19/2008 14:36:16,spid4,Unknown,Requested By:
12/19/2008 14:36:16,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: X SPID:3 ECID:0 Ec:(0x801F03C0) Value:0x7623ba00 Cost:(0/44)
12/19/2008 14:36:16,spid4,Unknown,Victim Resource Owner:
12/19/2008 14:36:16,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x49BD9A00) Value:0x90f3d820 Cost:(1/2710)
12/19/2008 14:36:16,spid4,Unknown,
12/19/2008 14:36:16,spid4,Unknown,
12/19/2008 14:36:16,spid4,Unknown,Wait-for graph
12/19/2008 14:36:16,spid4,Unknown,
12/19/2008 14:36:16,spid4,Unknown,Node:1
12/19/2008 14:36:16,spid4,Unknown,DB: 25 CleanCnt:1 Mode: X Flags: 0x0
12/19/2008 14:36:16,spid4,Unknown,
I check in sysobjects for exec dbo.QS_SoSSE25_VirtualFileStats but its not there in any database. It says Key lock on master and sysdtabases. I m not sure wht it says.
Can anyone give me a hint??? Thanks
December 23, 2008 at 11:24 am
Why does that stored procedure look like a third-party monitoring tool? My guess is if you search sysobjects in all databases you will find the procedure.
Are you using a third-party tool to monitor your instances and the manufacturer begins with a "Q"? Curious.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 23, 2008 at 12:31 pm
Yeah David,
I can see there is a database named QuestWorkdatabase. And we are using some Quest software as a third party tool for monitoring.So This is the reason for our deadlocks.. rite?
But SQL Server can detect any deadlock and kill one of the process then why it didn’t happen in my case? I was getting constant email through an alert that we set in my environment(Alert on SQL: Locks, Number of deadlocks/Sec on Page exceeds beyond threshold of 1 then create alert it was 2 in my case until we reboot our server)
December 23, 2008 at 12:45 pm
The app is deadlocking due to some potentially poorly written code or due to some poor statistics. Just a guess though.
A great article to help in this troubleshooting - http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 23, 2008 at 1:00 pm
Sorry, forgot to answer part, the alert is just telling you that you have had deadlocks. The database engine is resolving them. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply