December 7, 2007 at 10:40 am
I have SQL 2000 Analysis server running in production and every now and then i see the following error message in my NT - Application logs:
Prod1: OLAP Service EventLog - Application [error] [mssqlserverolapservice #124]: Relational data provider reported error: [Transaction (Process ID 125) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.:40001]
Is there a way, i could find out, what this process was ran? what approach should we take to solve this issues?
Appreciated any suggestions.
Thanks,
Cali
December 7, 2007 at 11:15 am
You might run PRofiler and filter based on the db, table, or some other object that you think is involved. Or just run it and then stop it when you get this message.
somewhere you have two transactions that are deadlocking each other (each wants the other's resources.
December 7, 2007 at 11:35 am
Unfortunately running profiler against AS - 2000 is not supported. This has been supported starting 2005. but AS - 2000 is pain to manage.
I am sure, there must be some way to capture this info. but what? i dont know yet.
December 8, 2007 at 1:30 pm
Run profiler against the SQL server that AS is connecting to. The deadlock's coming from the database, not AS.
You can alse try switching on traceflag 1204 (on SQL Server) so that the deadlock graph gets written into SQL's error log. Makes it fairly easy to find which two processes were involved in the deadlock and what they were deadlocked over.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2007 at 2:28 am
Hello Gila,
Thanks for the answer. Unfortunately i have Profiler running against my SQL 2000 box and i dont see deadlocks info in there. Also i looked at my SQL DB - Box and i dont find any blocking - deadlocks going on. My thought was, this might be coming from SQL AS box.
I shall enable the DBCC Trace on the DB Side and see, what i get. but is there any other way, i capture the deadlock info on DB box?
Thanks again,
Cali
December 9, 2007 at 2:42 am
Hi california,
Check this may it helps : http://msdn2.microsoft.com/en-us/library/ms186385.aspx
http://www.mssqltips.com/tip.asp?tip=1234
http://www.sqlservercentral.com/Forums/Topic396310-405-1.aspx
Regards,
Ahmed
December 9, 2007 at 11:17 pm
I my experience, the traceflag is the best way to get deadock info, as you don't need any form of monitoring running. You can see deadlock graphs in the error log
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2007 at 8:28 am
If you can, add the following trace flags to your SQL Server startup Parameters:
-T1204
-T3605
This way when a deadlock is encountered you will get 'extended' deadlock information logged to the SQL Server errorlog file. As for myself, these trace flags as start up Parameters are a part of my standard server build.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
December 10, 2007 at 11:02 am
Thansk everyone for contributing to this thread.
I have enabled 1204 and 3605 parameter and now i see a deadlock event logged into my SQL Error logs:
But the hard thing is, how to read and understand who the victim was and who caused the dead lock:
I have gone through the sql event logs and i noticed the following:
Line:1 ResType:LockOwner Stype:'OR' Mode: S SPID:130 ECID:0 Ec:(0x398C3590) Value:0x6a3c80e0 Cost:(0/0)
Line:2 Victim Resource Owner:
Line:3 ResType:LockOwner Stype:'OR' Mode: S SPID:130 ECID:0 Ec:(0x398C3590) Value:0x6a3c80e0 Cost:(0/0)
Line 4: Requested By:
Line 5: Input Buf: Language Event: exec SPCOPYCATEGORY OPERATIONS,[ACTUAL_FCST],[ACTUAL_FCST],[USD],[FXCTA],[SPSCOPE_700390],SPLOG_276231
Line 6: SPID: 62 ECID: 0 Statement Type: INSERT Line #: 1
Line 7: Owner:0x78cbaca0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0
Line 8: Grant List 1::
Line 9: PAG: 10:6:24721 CleanCnt:3 Mode: X Flags: 0x0
Line 10: Node:3
How to read and understand this logged info? Really appreciated if someone please suggest.
Many thanks again. You guy's are amazing.
Cali
December 10, 2007 at 11:36 am
Give this KB a whirl: http://support.microsoft.com/kb/832524
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
December 10, 2007 at 11:54 am
That doesn't look like the entire deadlock graph. Post the whole thing (in code blocks) please.
From the bit there, I can see the following.
The deadlock was between processes 130 and 62.
Process 62 was executing an insert (line 1) in the stored proc SPCOPYCATEGORY
The fact that it's line 1 suggests to me that it's actually a piece of dynamic SQL in that proc which caused the deadlock.
The input buffer for process 130 isn't there.
The deadlock resource looks like it is PAG: 10:6:24721
To see what object that page belongs to, run the following
DBCC TRACEON (3604)
DBCC PAGE(10,6,24721)
DBCC TRACEOFF (3604)
In the output there will be an objectID. Go to database 10 (identify it using Select DB_NAme(10)) then run SELECT object_name(<object_id here> )
btw, traceflag 3605 is not required to get the deadlock info written into the error log. 1204 alone will do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2007 at 12:07 pm
Not a day goes by when the forums at SSC do not teach me something new - thanks Gail !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
December 10, 2007 at 2:57 pm
Hello Gail,
Sorry for posting the partial dead lock info. please the detail dead lock info here. (I have copied everything from the SQL Server - error logs and pasted here.) Hope thats fine.
2007-12-10 08:54:25.30 spid4ResType:LockOwner Stype:'OR' Mode: S SPID:130 ECID:0 Ec:(0x398C3590) Value:0x6a
2007-12-10 08:54:25.30 spid4Victim Resource Owner:
2007-12-10 08:54:25.30 spid4ResType:LockOwner Stype:'OR' Mode: S SPID:130 ECID:0 Ec:(0x398C3590) Value:0x6a
2007-12-10 08:54:25.30 spid4Requested By:
2007-12-10 08:54:25.30 spid4Input Buf: Language Event: exec SPCOPYCATEGORY OPERATIONS,[ACTUAL_FCST],[ACTUAL
2007-12-10 08:54:25.30 spid4SPID: 62 ECID: 0 Statement Type: INSERT Line #: 1
2007-12-10 08:54:25.30 spid4Owner:0x78cbaca0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0
2007-12-10 08:54:25.30 spid4Grant List 1::
2007-12-10 08:54:25.30 spid4PAG: 10:6:24721 CleanCnt:3 Mode: X Flags: 0x0
2007-12-10 08:54:25.30 spid4Node:3
2007-12-10 08:54:25.30 spid4
2007-12-10 08:54:25.30 spid4ResType:LockOwner Stype:'OR' Mode: IS SPID:137 ECID:0 Ec:(0x6DC99590) Value:0x6
2007-12-10 08:54:25.30 spid4Requested By:
2007-12-10 08:54:25.30 spid4Input Buf: RPC Event: sp_prepexec;1
2007-12-10 08:54:25.30 spid4SPID: 130 ECID: 0 Statement Type: SELECT Line #: 1
2007-12-10 08:54:25.30 spid4Owner:0x6a3c80e0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:130 ECID:0
2007-12-10 08:54:25.30 spid4Wait List:
2007-12-10 08:54:25.30 spid4PAG: 10:6:24721 CleanCnt:3 Mode: X Flags: 0x0
2007-12-10 08:54:25.30 spid4Node:2
2007-12-10 08:54:25.30 spid4
2007-12-10 08:54:25.30 spid4ResType:LockOwner Stype:'OR' Mode: X SPID:62 ECID:0 Ec:(0x57BF9568) Value:0x545
2007-12-10 08:54:25.30 spid4Requested By:
2007-12-10 08:54:25.30 spid4Input Buf: Language Event: SELECT [TIMEID],[ACCOUNT_OPERATIONS],SIGNEDDATA...
2007-12-10 08:54:25.30 spid4SPID: 137 ECID: 0 Statement Type: SELECT INTO Line #: 1
2007-12-10 08:54:25.30 spid4Owner:0x619b7f00 Mode: IS Flg:0x0 Ref:1 Life:00000000 SPID:137 ECID:0
2007-12-10 08:54:25.30 spid4Grant List 0::
2007-12-10 08:54:25.30 spid4PAG: 10:1:137637 CleanCnt:2 Mode: IS Flags: 0x2
2007-12-10 08:54:25.30 spid4Node:1
2007-12-10 08:54:25.30 spid4
2007-12-10 08:54:25.30 spid4Wait-for graph
2007-12-10 08:54:25.30 spid4
2007-12-10 08:54:25.30 spid4...
2007-12-10 08:54:30.30 spid4ResType:LockOwner Stype:'OR' Mode: IS SPID:137 ECID:0 Ec:(0x6DC99590) Value:0x6
2007-12-10 08:54:30.30 spid4Victim Resource Owner:
2007-12-10 08:54:30.30 spid4ResType:LockOwner Stype:'OR' Mode: IS SPID:137 ECID:0 Ec:(0x6DC99590) Value:0x6
2007-12-10 08:54:30.30 spid4Requested By:
2007-12-10 08:54:30.30 spid4Input Buf: RPC Event: sp_prepexec;1
2007-12-10 08:54:30.30 spid4SPID: 105 ECID: 0 Statement Type: SELECT Line #: 1
2007-12-10 08:54:30.30 spid4Owner:0x6159c100 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:105 ECID:0
2007-12-10 08:54:30.30 spid4Wait List:
2007-12-10 08:54:30.30 spid4PAG: 10:6:24721 CleanCnt:3 Mode: X Flags: 0x0
2007-12-10 08:54:30.30 spid4Node:3
2007-12-10 08:54:30.30 spid4
2007-12-10 08:54:30.30 spid4ResType:LockOwner Stype:'OR' Mode: X SPID:62 ECID:0 Ec:(0x57BF9568) Value:0x545
2007-12-10 08:54:30.30 spid4Requested By:
2007-12-10 08:54:30.30 spid4Input Buf: Language Event: SELECT [TIMEID],[ACCOUNT_OPERATIONS],SIGNEDDATA...
2007-12-10 08:54:30.30 spid4SPID: 137 ECID: 0 Statement Type: SELECT INTO Line #: 1
2007-12-10 08:54:30.30 spid4Owner:0x619b7f00 Mode: IS Flg:0x0 Ref:1 Life:00000000 SPID:137 ECID:0
2007-12-10 08:54:30.30 spid4Grant List 0::
2007-12-10 08:54:30.30 spid4PAG: 10:1:137637 CleanCnt:2 Mode: IS Flags: 0x2
2007-12-10 08:54:30.30 spid4Node:2
2007-12-10 08:54:30.30 spid4
2007-12-10 08:54:30.30 spid4ResType:LockOwner Stype:'OR' Mode: S SPID:105 ECID:0 Ec:(0x67D5B590) Value:0x61
2007-12-10 08:54:30.30 spid4Requested By:
2007-12-10 08:54:30.30 spid4Input Buf: Language Event: exec SPCOPYCATEGORY OPERATIONS,[ACTUAL_FCST],[ACTUAL
2007-12-10 08:54:30.30 spid4SPID: 62 ECID: 0 Statement Type: INSERT Line #: 1
2007-12-10 08:54:30.30 spid4Owner:0x78cbaca0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0
2007-12-10 08:54:30.30 spid4Grant List 1::
2007-12-10 08:54:30.30 spid4PAG: 10:6:24721 CleanCnt:3 Mode: X Flags: 0x0
2007-12-10 08:54:30.30 spid4Node:1
2007-12-10 08:54:30.30 spid4
2007-12-10 08:54:30.30 spid4Wait-for graph
2007-12-10 08:54:30.30 spid4
2007-12-10 08:54:30.30 spid4...
Thanks again for all your help Gail.
Cali
December 11, 2007 at 1:33 am
I would like to share this link with all of you.
Good one
http://www.code-magazine.com/article.aspx?quickid=0309101&page=1
"More Green More Oxygen !! Plant a tree today"
December 11, 2007 at 2:17 am
It's fine. Just to interpret the graph requires most of it. I'll note which lines say what, so that you can see how I read the graph. (I just numbered the lines of the deadock as you posted them) Let's see....
The deadlock resources were both database pages (lines 9, 18, 27, 41, 50, 59)
10:6:24721 and 10:1:137637. You can use DBCC page to idetify the objects that own those pages.
Deadlock participants:
Spid 130 - Running exec SPCOPYCATEGORY (line 5). Deadlock ccured in an insert, listed as on line 1 (line 6) I believe that to be a piece of dynamic SQL, since that's the only way to get an operation on line 1 of a stored proc.
Spid 137 - Running sp_prepexec. Doing a select. Can't see anything more about this.
Spid 62 - Running a select into. Not part of a stored procedure. (line 46)
Spid 105. Runing the same procedure and same line as Spid 130 was (line 55,56)
Does that help somewhat?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply