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 better monitoring for locks and blocking in SQL2005?

Are the facilities for monitoring locks and blocking in SQL2005 better than
2000?
I found that in SQL2000 Enterprise Manager by the time you waited for
"current activity" to refresh, the information could have been and gone.
I'd be interested to hear if you've been using it sucessfully to do this
sort of thing.
If you want something similar to Current Activity, Activity
Monitor in Management Studio is an improvement over the
Current Activity node in Enterprise Manager. Opens in a
separate window, you have a few different view to chose
from, you can set a refresh interval.
2005 also has server level reports such as Blocking
Transactions and Top Transactions by Locks.
But just like Current Activity, you are seeing a snapshot
and you need to refresh. You have more options in 2000 to
understand the blocking, locking if you use queries to
monitor activity and it's somewhat the same in 2005.
However, there are improvements to the monitoring and
details you can get related to locking, blocking. In 2005,
you have a lot more details exposed on the waits. You can
also set up a blocked process threshold and use this with
the Blocked Process Report event class in Profiler, SQL
trace. You can also get detailed historical information
related to locking and blocking from the DMV
Sys.dm_db_index_operational_stats.
The following article has a lot of information on you can
use to monitor blocking, locking issues in 2005:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pro.../tsprfprb.mspx
-Sue
On Thu, 19 Jan 2006 04:50:02 -0800, "James"
<James@.discussions.microsoft.com> wrote:

>Are the facilities for monitoring locks and blocking in SQL2005 better than
>2000?
>I found that in SQL2000 Enterprise Manager by the time you waited for
>"current activity" to refresh, the information could have been and gone.
>I'd be interested to hear if you've been using it sucessfully to do this
>sort of thing.
|||Night and day, across the board. SQL Server 2000 exposed very limited
information to be able to monitor. SQL Server 2005 has layer after layer
after layer of diagnostics. From DBCC commans through DMVs/DMFs, there
aren't many areas of the engine you can't get at.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"James" <James@.discussions.microsoft.com> wrote in message
news:98B66E56-8895-4040-BA63-056144C882C6@.microsoft.com...
> Are the facilities for monitoring locks and blocking in SQL2005 better
> than
> 2000?
> I found that in SQL2000 Enterprise Manager by the time you waited for
> "current activity" to refresh, the information could have been and gone.
> I'd be interested to hear if you've been using it sucessfully to do this
> sort of thing.