September 4, 2008 at 4:27 am
Hi,
Seem to have an issue with a rogue spid, details as follows;
Numerous entries in SQL log relating to-
2008-09-04 10:28:13.99 spid123 WaitForChildren: Children persisting. Retry: 68144
SP_who returns details of SPID 123 - Database = Master, status sleeping, not processing in 16 days, wait type = EC
SPID 123 is sleeping and has not done any processing in 16 days
SPID 123 was selecting some data from a temporary table as follows.
select replace(cast(TextData as varchar), 'Deadlock Chain SPID = ', '') FROM #deadlocktrace where textdata like '%Deadlock Chain SPID%' union select SPID FROM #deadlocktrace where textdata like '%Deadlock Cha
Kill 123 results in
Server: Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.
Implication is that spid 123 is a system process(?) - which it isn't
My questions are, what does WaitForChildren: Children persisting. Retry actually mean? and how can I kill SPID 123?
Any suggestions gratefully received.
September 4, 2008 at 4:56 am
Please check this bugfix :
http://support.microsoft.com/kb/319184
This hotfix states "This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3"
Which is your sqlsever @@version ?
(maybe it's time to implement sp4)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 4, 2008 at 9:11 am
Thanks for the reply, the server is on SQL sp 4
September 4, 2008 at 11:48 pm
You didn't post the @@version.
This is a sqlserver parallelism issue.
Did you also apply Cumulative HofFix for sp4(build 2187) - SQL2000-KB916287-v8.00.2187 ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 2:07 am
As requested
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
September 5, 2008 at 2:23 am
8.00.2039 is the regular SP4
Is this a multi processor or multi core box ?
if yes: IMO your should apply the cumulative hotfix I mentioned earlier.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 2:30 am
SQL2000-KB916287-v8.00.2187 has not been applied
September 5, 2008 at 3:09 am
are you using AWE ? http://support.microsoft.com/kb/899761
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 4:00 am
Hi,
AWE is not enabled
September 5, 2008 at 4:11 am
Can you apply the workaround (i.e. disable parallelisme) ?
from KB319184
WORKAROUND
To work around this behavior you can either:
•Use the query hint (MAXDOP=1) to avoid a parallel query plan.
-or-
•Set the max degree of parallelism configuration value to 1.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 5:50 am
I'm happy to give this a try, but am unsure of the effects of setting the max degree of parallelism configuration value to 1.
September 5, 2008 at 6:06 am
Everything will still work, but will only use a single db-thread to accomplish the queries.
So it may need more time to complete, but your error will nolonger occur.
You may want to give it a try befor you contact PSS...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 8:06 am
Thank you for the suggestions, I'll try the paralellism and apply the hotfix. The server is going to get a reboot this evening as well.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply