Any way to kill zombie locks by commandline?

hi
we have some troubles with a object that kills our SQL2000SP4. CPU spikes to
100% and stay there. We are searching for a solution, but currently don't
have one! So it comes to situation where a object named
"company.dbo.fn_example" have more then one ProcessID. If this occour -
"all" other processes are zombies and i only know how to kill them by hand.
this makes me *stressed*, while i must kill 10 every 20 minutes.
is there any way to kill the object for e.g. with all processes inside - by
command line?
Regards
MarcSounds like a very poorly written function that is monopolizing the CPU's.
Try setting MAXDOP to 1 and see if it stays contained to just one CPU. But
I would look at other ways to optimize the statements that use this
function.
--
Andrew J. Kelly SQL MVP
"Marc Bauer" <marc.bau@.gmx.net> wrote in message
news:%23IxhtuvLGHA.2300@.TK2MSFTNGP15.phx.gbl...
> hi
> we have some troubles with a object that kills our SQL2000SP4. CPU spikes
> to 100% and stay there. We are searching for a solution, but currently
> don't have one! So it comes to situation where a object named
> "company.dbo.fn_example" have more then one ProcessID. If this occour -
> "all" other processes are zombies and i only know how to kill them by
> hand. this makes me *stressed*, while i must kill 10 every 20 minutes.
> is there any way to kill the object for e.g. with all processes inside -
> by command line?
>
> Regards
> Marc
>|||hi
> Sounds like a very poorly written function that is monopolizing the CPU's.
> Try setting MAXDOP to 1 and see if it stays contained to just one CPU.
> But I would look at other ways to optimize the statements that use this
> function.
our database specialist, reviewed the function and said - that this one is
correct and should work very fast. but sometimes - if this function gets
executed very very often it comes to something like a deadlock situation.
then all tables are locked... i have had a look to the function today, and
there is everywhere in the selects a with(nolock) used. so it cannot be a
read deadlock.
so - today - we don't know what's wrong. maybe a bug in SQL and we need to
find a workaround... this should be partly a con job with a kill - until we
found a solution.
Regards
Marc|||Can you post the function?
--
Andrew J. Kelly SQL MVP
"Marc Bauer" <marc.bau@.gmx.net> wrote in message
news:unyGffyLGHA.420@.tk2msftngp13.phx.gbl...
> hi
>> Sounds like a very poorly written function that is monopolizing the
>> CPU's. Try setting MAXDOP to 1 and see if it stays contained to just one
>> CPU. But I would look at other ways to optimize the statements that use
>> this function.
> our database specialist, reviewed the function and said - that this one is
> correct and should work very fast. but sometimes - if this function gets
> executed very very often it comes to something like a deadlock situation.
> then all tables are locked... i have had a look to the function today, and
> there is everywhere in the selects a with(nolock) used. so it cannot be a
> read deadlock.
> so - today - we don't know what's wrong. maybe a bug in SQL and we need to
> find a workaround... this should be partly a con job with a kill - until
> we found a solution.
>
> Regards
> Marc
>|||On Sat, 11 Feb 2006 17:26:09 +0100, "Marc Bauer" <marc.bau@.gmx.net>
wrote:
> but sometimes - if this function gets
>executed very very often it comes to something like a deadlock situation.
>then all tables are locked...
What do you mean, "very very often"?
Does the calling code happen to use #temp tables?
If you post source code, please also post what you can of the
invocation.
J.|||we found a endless loop in the function... :-(
Marc

Any true public variables for custom code section?

I have been searching the newsgroup for showing the group sum in the
footer section. This isn't the same as a normal group sum as the
amount shown in the textbox is using the value straight out of the
dataset - =Fields!Amt.Value, instead of =SUM(Fields!Amt.Value). I then
wanted to show the sum of the group amounts in the footer.
I thought I had found a solution by calling a function to store the
sum and then show the total by calling the same function with a
parameter to return the accumulated amount. Unfortunately I hit
another brick wall with this approach because even though I declared a
public variable outside the function (also tried a static within the
function), the accumulated amount only remains within the scope of
the group calling the function. When the footer section calls the same
function the accumulator is 0 instead of the accumulated amount for
the group.
My question, how do you declare and access a true public variable?
Here's an example of what is happening:
The group amount only needs to be displayed within the group, but
summed at the end of the report
Report
--
Group1 (grouped by MbGUID)
Textbox = AddGroupTotal("A", Fields!SizeTtl.Value)
Footer
Textbox = AddGroupTotal("T", 0) ' Note the "0" is just a
placeholder
Dataset columns
--
MbGUID,SizeTtl,Year,Month,Week,MaxSize
Data
--
1,120,2006,1,2,80
1,120,2006,1,3,90
1,120,2006,1,4,92
2,130,2006,1,3,87
2,130,2005,1,4,120
The output with the custom code
--
MbGuid Total
1 120
2 130
Report Total: 0
Obviously the Report Total should show 250 instead of 0.
Custom Code Section
--
Public ttl As Double = 0
Public Function AddGroupTotal(pMode As String, pNbr As Long) As Long
'Static Ttl AS Long
If pMode = "A" Then
If Ttl = Nothing Then
Ttl = 0
End If
Ttl = Ttl + pNbr
' Runtime error stating parameter is read only for the below
statement
'Report.Parameters!pSizeTtl = pNbr
AddGroupTotal = pNbr
ElseIf pMode = "T" Then
AddGroupTotal = Ttl
End If
End Function
MarkI'm also having the same problem.
I'm attempting to build a public array in the details section of a table and
then perform calculations against that, now filled array in the group footer.
The problem is that when I call my custom AddToArray code in the details
section and then return the ubound value of the array, it works fine in the
details section but when it gets to the footer the array is empty again.
Anyone have any suggestions?
"Mark" wrote:
> I have been searching the newsgroup for showing the group sum in the
> footer section. This isn't the same as a normal group sum as the
> amount shown in the textbox is using the value straight out of the
> dataset - =Fields!Amt.Value, instead of =SUM(Fields!Amt.Value). I then
> wanted to show the sum of the group amounts in the footer.
> I thought I had found a solution by calling a function to store the
> sum and then show the total by calling the same function with a
> parameter to return the accumulated amount. Unfortunately I hit
> another brick wall with this approach because even though I declared a
> public variable outside the function (also tried a static within the
> function), the accumulated amount only remains within the scope of
> the group calling the function. When the footer section calls the same
> function the accumulator is 0 instead of the accumulated amount for
> the group.
> My question, how do you declare and access a true public variable?
> Here's an example of what is happening:
> The group amount only needs to be displayed within the group, but
> summed at the end of the report
> Report
> --
> Group1 (grouped by MbGUID)
> Textbox = AddGroupTotal("A", Fields!SizeTtl.Value)
> Footer
> Textbox = AddGroupTotal("T", 0) ' Note the "0" is just a
> placeholder
> Dataset columns
> --
> MbGUID,SizeTtl,Year,Month,Week,MaxSize
> Data
> --
> 1,120,2006,1,2,80
> 1,120,2006,1,3,90
> 1,120,2006,1,4,92
> 2,130,2006,1,3,87
> 2,130,2005,1,4,120
> The output with the custom code
> --
> MbGuid Total
> 1 120
> 2 130
> Report Total: 0
> Obviously the Report Total should show 250 instead of 0.
>
> Custom Code Section
> --
> Public ttl As Double = 0
> Public Function AddGroupTotal(pMode As String, pNbr As Long) As Long
> 'Static Ttl AS Long
> If pMode = "A" Then
> If Ttl = Nothing Then
> Ttl = 0
> End If
> Ttl = Ttl + pNbr
> ' Runtime error stating parameter is read only for the below
> statement
> 'Report.Parameters!pSizeTtl = pNbr
> AddGroupTotal = pNbr
> ElseIf pMode = "T" Then
> AddGroupTotal = Ttl
> End If
> End Function
> Mark
>