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 solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any one tell export Sql Database in access hole object

hi All
I want to export my mssqlserver 2000 database with all relation ship and all
default value and all data save in sqlserver Database (I want to export hole
data base object ) in MS Access database is it possible
if it is possible from mssqlserver please tell me if it is possible from any
other third party tool then please tell me
thanks in advanced
from
khurram
I am not aware of tool that does a complete transfer of all objects from SQL
Server to MS Access. You can certainly use SQL Server DTS to move data to
Access. You might want to post this to an Access group as well.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:O1N0qQnZEHA.3988@.tk2msftngp13.phx.gbl...
hi All
I want to export my mssqlserver 2000 database with all relation ship and all
default value and all data save in sqlserver Database (I want to export hole
data base object ) in MS Access database is it possible
if it is possible from mssqlserver please tell me if it is possible from any
other third party tool then please tell me
thanks in advanced
from
khurram
|||Hi,
You can either use Upsizing wizard or SQL DTS to upgrade objects/data from
Access to SQL server.
The details about the tools and steps are detailed in the below
knowledgebase article.
http://support.microsoft.com/default...b;en-us;237980
Thanks
Hari
MCDBA"khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:O1N0qQnZEHA.3988@.tk2msftngp13.phx.gbl...
> hi All
> I want to export my mssqlserver 2000 database with all relation ship and
all
> default value and all data save in sqlserver Database (I want to export
hole
> data base object ) in MS Access database is it possible
> if it is possible from mssqlserver please tell me if it is possible from
any
> other third party tool then please tell me
> thanks in advanced
> from
> khurram
>