Kind of Replication will work ?

HI
We have 1 Central office Corporate Office and three branch offices. We
are in process of developing an Order Management Application, Every
branch will enter Inquiries/Quotation/Orders into application and at the
end of day we want all data entered at every branch to be available at
corporate office for further processing, can you please advise me kind
of replication will work for us. Thanks in advance
Best Regards
Shahid Saleem
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
It looks like you only want data flowing one way, from the branch offices to
the central office. In this case I would use transactional replication.
The next question you have to ask your self is, is there anything unique
about your data in the branch office which makes it identifiable as
belonging to that branch? If so, you should filter on this data, and when
you are in the specify articles dialog for the publication, click on the
browse button to the right of your table name, select the snapshot tab, and
select the delete all data in the existing table that matches the row filter
statement.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Shahid Saleem" <shahid_mcsd@.hotmail.com> wrote in message
news:e3l4fOx8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> HI
> We have 1 Central office "Corporate Office" and three branch offices. We
> are in process of developing an Order Management Application, Every
> branch will enter Inquiries/Quotation/Orders into application and at the
> end of day we want all data entered at every branch to be available at
> corporate office for further processing, can you please advise me kind
> of replication will work for us. Thanks in advance
> Best Regards
> Shahid Saleem
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Killing xp_cmdshell

Hi.
I have run xp_cmdshell from within a stored procedure, and it has hung. Is
there any way to kill the process, other than rebooting SQL Server?
Cheers
NeilIf you do not have luck using "kill spid", go to the server and end the tas
k
using "task manager".
AMB
"NeilDJones" wrote:

> Hi.
> I have run xp_cmdshell from within a stored procedure, and it has hung. Is
> there any way to kill the process, other than rebooting SQL Server?
> Cheers
> Neil

Killing the process automatically

Hi everybody,

We have a very large database and high transaction volume. Time to time
these transactions are locking each other and decrease the performance
of the database. Is there any way that I can automate the killing
process when blocking and deadlock time is exceeded in certain time
elipsade? Can somebody help me on this please?

Regards

asa.laststubborn wrote:
> Hi everybody,
> We have a very large database and high transaction volume. Time to time
> these transactions are locking each other and decrease the performance
> of the database. Is there any way that I can automate the killing
> process when blocking and deadlock time is exceeded in certain time
> elipsade? Can somebody help me on this please?

If SQL Server detects a deadlock it will kill one of the two involved TX
automatically. But you should really change your app to prevent these
deadlocks.

You probably cannot do much about normal locking as this is expected
behavior other than probably optimizing your SQL to make it faster.

HTH

robert|||Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??

Thanks|||laststubborn wrote:
> Is it possible to change this deadlock killing time? for instance lets
> say instead of 5 min change it to 2 min??

read the docs (BOL)

Customizing the Lock Time-out
When Microsoft SQL Server 2000 cannot grant a lock to a transaction on
a resource because another transaction already owns a conflicting lock
on that resource, the first transaction becomes blocked waiting on that
resource. If this causes a deadlock, SQL Server terminates one of the
participating transactions (with no time-out involved). If there is no
deadlock, the transaction requesting the lock is blocked until the other
transaction releases the lock. By default, there is no mandatory
time-out period, and no way to test if a resource is locked before
locking it, except to attempt to access the data (and potentially get
blocked indefinitely).

robert|||laststubborn (arafatsalih@.gmail.com) writes:
> Is it possible to change this deadlock killing time? for instance lets
> say instead of 5 min change it to 2 min??

A deadlock does not take five minutes to sort out. It seems that you
have a misconception of what a deadlock is. A deadlock is when two
processes are blocking each other, so none of them can continue. This
is something that SQL Server detects automatically. It usually takes a
couple of seconds.

But one long-running process can block other processes (than in their
turn can block other processes etc) without any deadlock to occur.

I would advice against any automatic killing, as supposedly some processes
are more important than others. It's better to analyse what those blockers
are up to, and if the queries can be improved, or indexes added to
speed up these queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
queries.. With this, no locks would actually happen.|||"D0MZE" <domze.sa@.gmail.com> wrote in message
news:1144891406.165135.214850@.i40g2000cwc.googlegr oups.com...
> Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
> queries.. With this, no locks would actually happen.

Not quite.

For a select it basically means to ignore locks on rows.

This can mean you can get phantom rows, not get rows you should etc. i.e.
you'll get an inconsistent view of the table at the time.

This MAY be acceptable in some circumstances, but in others would be
completely verbotin. (imagine an ATM that did a look up on cache available
with a (NOLOCK) while your bank is deleting your last check. You'd falsely
be told you have more money available than you actually do and could
overdraw the account.)

killing sqlservr.exe from sqlclr code

I keep getting different answers from different people on regarding if you can or cannot kill the hosting sql server process with an unsafe assembly. Can you do this? If so could you please attach a sample demonstrating this?

Thanks,

Derek

Uhh, delicate question. Sure Microsoft did everything to prevent this. I know that in beta this was sort of unstable, but right now I have no clue how to do this.
If someone has an example to do this, this should be reported as a bug to Microsoft in order to prevent this. But I would assume that the CLR hosting process is quite stable right now.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||There are many ways to kill sql server. If we allow PInvoke or unsafe code, then the possibilities are endless. There is no way to prevent a user from calling TerminateProcess with PInvoke. We are more concerned with safe assemblies not corrupting the sql server process then a user focused on terminating sql server.

You can always write an XP to do the same anyway for instance.|||could you please give me some quick code in C#/VB.Net that will kill the hosting process in that case?|||I am only concerned about this becaseu I am a lead author on a sqlclr book.|||

Is this simple enough?

public static void killsql()
{
System.Environment.Exit(-1);
}

Steven

Killing SPIDs

Is there any other way to kill a process besides using kill?
I have a spid that will not die..and has been in the killed/rollback for
roughly 2hrs with the current wait reason as Waiting on OLEDB provider
The procedure the spid is calling uses an opendatasource to a Oracle server.Hi,
KILL is the only command available to remove a partcular SPID from SQL
Server. To reove all the connections connected to a database use
alter database <dbname> set single_user with rollback immediate
To get the status of KILL command you could use :-
KILL <SPID> WITH STATUSONLY
WITH STATUSONLY
Specifies that SQL Server generate a progress report on a given spid or UOW
that is being rolled back. The KILL command with WITH STATUSONLY does not
terminate or roll back the spid or UOW. It only displays the current
progress report.
Thanks
Hari
SQL Server MVP
"Gary" <clgary@.yahoo.com> wrote in message
news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Is there any other way to kill a process besides using kill?
> I have a spid that will not die..and has been in the killed/rollback for
> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
> The procedure the spid is calling uses an opendatasource to a Oracle
> server.
>|||Hi
With linked servers, stop MSDTC and re-start it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Gary" <clgary@.yahoo.com> wrote in message
news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Is there any other way to kill a process besides using kill?
> I have a spid that will not die..and has been in the killed/rollback for
> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
> The procedure the spid is calling uses an opendatasource to a Oracle
> server.
>|||It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>> Is there any other way to kill a process besides using kill?
>> I have a spid that will not die..and has been in the killed/rollback for
>> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
>> The procedure the spid is calling uses an opendatasource to a Oracle
>> server.
>|||OPENDATASOURCE in a SQL Agent Job? A stored procedure? Ad hoc T-SQL from a
user or a remote process? Or, are you using DTS?
Chances are that SQL Server had to "go preimptive" and spawn an external
thread. You could check the task list and kill whatever thread was hung
keeping the KILL from ROLLING BACK.
Sincerely,
Anthony Thomas
"Gary" <clgary@.yahoo.com> wrote in message
news:OwXadQuaFHA.1456@.TK2MSFTNGP15.phx.gbl...
It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>> Is there any other way to kill a process besides using kill?
>> I have a spid that will not die..and has been in the killed/rollback for
>> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
>> The procedure the spid is calling uses an opendatasource to a Oracle
>> server.
>

Killing SPIDs

Is there any other way to kill a process besides using kill?
I have a spid that will not die..and has been in the killed/rollback for
roughly 2hrs with the current wait reason as Waiting on OLEDB provider
The procedure the spid is calling uses an opendatasource to a Oracle server.
Hi,
KILL is the only command available to remove a partcular SPID from SQL
Server. To reove all the connections connected to a database use
alter database <dbname> set single_user with rollback immediate
To get the status of KILL command you could use :-
KILL <SPID> WITH STATUSONLY
WITH STATUSONLY
Specifies that SQL Server generate a progress report on a given spid or UOW
that is being rolled back. The KILL command with WITH STATUSONLY does not
terminate or roll back the spid or UOW. It only displays the current
progress report.
Thanks
Hari
SQL Server MVP
"Gary" <clgary@.yahoo.com> wrote in message
news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Is there any other way to kill a process besides using kill?
> I have a spid that will not die..and has been in the killed/rollback for
> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
> The procedure the spid is calling uses an opendatasource to a Oracle
> server.
>
|||Hi
With linked servers, stop MSDTC and re-start it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Gary" <clgary@.yahoo.com> wrote in message
news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Is there any other way to kill a process besides using kill?
> I have a spid that will not die..and has been in the killed/rollback for
> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
> The procedure the spid is calling uses an opendatasource to a Oracle
> server.
>
|||It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
|||OPENDATASOURCE in a SQL Agent Job? A stored procedure? Ad hoc T-SQL from a
user or a remote process? Or, are you using DTS?
Chances are that SQL Server had to "go preimptive" and spawn an external
thread. You could check the task list and kill whatever thread was hung
keeping the KILL from ROLLING BACK.
Sincerely,
Anthony Thomas

"Gary" <clgary@.yahoo.com> wrote in message
news:OwXadQuaFHA.1456@.TK2MSFTNGP15.phx.gbl...
It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>

Killing SPIDs

Is there any other way to kill a process besides using kill?
I have a spid that will not die..and has been in the killed/rollback for
roughly 2hrs with the current wait reason as Waiting on OLEDB provider
The procedure the spid is calling uses an opendatasource to a Oracle server.Hi,
KILL is the only command available to remove a partcular SPID from SQL
Server. To reove all the connections connected to a database use
alter database <dbname> set single_user with rollback immediate
To get the status of KILL command you could use :-
KILL <SPID> WITH STATUSONLY
WITH STATUSONLY
Specifies that SQL Server generate a progress report on a given spid or UOW
that is being rolled back. The KILL command with WITH STATUSONLY does not
terminate or roll back the spid or UOW. It only displays the current
progress report.
Thanks
Hari
SQL Server MVP
"Gary" <clgary@.yahoo.com> wrote in message
news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Is there any other way to kill a process besides using kill?
> I have a spid that will not die..and has been in the killed/rollback for
> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
> The procedure the spid is calling uses an opendatasource to a Oracle
> server.
>|||Hi
With linked servers, stop MSDTC and re-start it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Gary" <clgary@.yahoo.com> wrote in message
news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Is there any other way to kill a process besides using kill?
> I have a spid that will not die..and has been in the killed/rollback for
> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
> The procedure the spid is calling uses an opendatasource to a Oracle
> server.
>|||It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>|||OPENDATASOURCE in a SQL Agent Job? A stored procedure? Ad hoc T-SQL from a
user or a remote process? Or, are you using DTS?
Chances are that SQL Server had to "go preimptive" and spawn an external
thread. You could check the task list and kill whatever thread was hung
keeping the KILL from ROLLING BACK.
Sincerely,
Anthony Thomas
"Gary" <clgary@.yahoo.com> wrote in message
news:OwXadQuaFHA.1456@.TK2MSFTNGP15.phx.gbl...
It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>

killing process

Hi everyone,
There was some process I wanted to kill with KILL command but I couldnt
because it was said that it was rolling back transaction and time left is 0
ms(?). LastBatch in sp_who2 showed time around two weeks ago. The process
seemed to be doing nothing.
Eventually I had to restart server (development environment, so no problems
with that), but are there any other way to get rid of such processes ?
Thanks a lot for any info
Alex
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||This is typical of an external process called by SQL Agent that has been
hung up on some external issue. Before you kill the spid, you need to tell
the SQL Agent job to cancel the activity. Then, if the spid remains, you
can kill it. Otherwise, the SQL Agent then gets hung on releasing hooks.
This is typical of a failed sqlmaint process hung on a failed MAPI client or
ActiveX process through xp_cmdshell.
Sincerely,
Anthony Thomas

"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:O7$Tdfn2EHA.2012@.TK2MSFTNGP15.phx.gbl...
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

killing process

Hi everyone,
There was some process I wanted to kill with KILL command but I couldnt
because it was said that it was rolling back transaction and time left is 0
ms(?). LastBatch in sp_who2 showed time around two weeks ago. The process
seemed to be doing nothing.
Eventually I had to restart server (development environment, so no problems
with that), but are there any other way to get rid of such processes ?
Thanks a lot for any info
AlexYou would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||This is typical of an external process called by SQL Agent that has been
hung up on some external issue. Before you kill the spid, you need to tell
the SQL Agent job to cancel the activity. Then, if the spid remains, you
can kill it. Otherwise, the SQL Agent then gets hung on releasing hooks.
This is typical of a failed sqlmaint process hung on a failed MAPI client or
ActiveX process through xp_cmdshell.
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:O7$Tdfn2EHA.2012@.TK2MSFTNGP15.phx.gbl...
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Killing Locks by Object - SS2005

splocI can't restore a database due to a locking issue. While I've killed
the Process in Activity Monitor, I still see the database listed on the Locks
By Object page. The Process ID is a negative number. Any ideas as to what I
can do to get rid of this lock?
Here's the output from sp_lock: -2 7 0 0 DB
S GRANT
Thanks in advance.
JohnOn Oct 5, 1:49 am, John Roberts
<JohnRobe...@.discussions.microsoft.com> wrote:
> splocI can't restore a database due to a locking issue. While I've killed
> the Process in Activity Monitor, I still see the database listed on the Locks
> By Object page. The Process ID is a negative number. Any ideas as to what I
> can do to get rid of this lock?
> Here's the output from sp_lock: -2 7 0 0 DB
> S GRANT
> Thanks in advance.
> John
After killing the process you may try putting database in single user
mode which would prevent application or user establishing connection.
Thanks
VS|||Thanks for the response..
When I do a select distinct req_transactionuow, req_transactionID from
syslockinfo where req_spid = -2
I see the follwing:
req_transactionuow req_transactionID
--
--
00000000-0000-0000-0000-000000000000 0
When I try to kill this UOW using the guid of all zeroes, we get the
following error:
Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000}
does not exist.
Anybody out there familiar with killing orphaned transactions where the UOW
GUID is all zeros? My only solution now is to restart the service and, as
you might have imagined, that's NOT the only database running!!
Thanks in advance.
John
"vijay" wrote:
> On Oct 5, 1:49 am, John Roberts
> <JohnRobe...@.discussions.microsoft.com> wrote:
> > splocI can't restore a database due to a locking issue. While I've killed
> > the Process in Activity Monitor, I still see the database listed on the Locks
> > By Object page. The Process ID is a negative number. Any ideas as to what I
> > can do to get rid of this lock?
> >
> > Here's the output from sp_lock: -2 7 0 0 DB
> > S GRANT
> >
> > Thanks in advance.
> >
> > John
> After killing the process you may try putting database in single user
> mode which would prevent application or user establishing connection.
> Thanks
> VS
>

Killing a User Process

Hi All
Here is the scenario, a user has a query running which has
taken far too long and needs to be killed. This is a front
end and the user does not have access to SQL Server.
1. Can that user Kill the Process.
2. What wuld be the code to do that?
Many Thanks
PaulHi Paul,
1. Most likely not. To kill a SQL Server process you have to be a member of
the sysadmin or processadmin roles, and ordinary users usually aren't.
2. KILL <spid> where spid is the process is of the process.
Note that KILLing a process will cause all the transactions that have been
started by the process to be rolled back, so it can take some time before
the process is actually really killed.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"paul Blackler" <pblackler@.themis.co.uk> wrote in message
news:06ae01c37081$75949630$a601280a@.phx.gbl...
> Hi All
> Here is the scenario, a user has a query running which has
> taken far too long and needs to be killed. This is a front
> end and the user does not have access to SQL Server.
> 1. Can that user Kill the Process.
> 2. What wuld be the code to do that?
> Many Thanks
> Paul

killing a user process

Hi,
I have a user process related to sql mail (xp_readmail) which was submitted.
It was taking a longtime to execute, hence I tried to kill this process using
KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
again execute the KILL command WITH_STATUS_ONLY, it says rollback is
completed successfully. But still the process is not killed and it stays in
this mode for indefinite time.
How can I kill such kind of process? I even tried re-starting
SQLServeragent, but it didn't help. Anybody is facing such problems?
Thanks
GYKIt's running mapi and you may need to use the command line
kill to kill the mapi32 OS process - or use task manager and
kill the processes.
-Sue
On Tue, 28 Sep 2004 12:15:03 -0700, GYK
<GYK@.discussions.microsoft.com> wrote:
>Hi,
>I have a user process related to sql mail (xp_readmail) which was submitted.
>It was taking a longtime to execute, hence I tried to kill this process using
>KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
>again execute the KILL command WITH_STATUS_ONLY, it says rollback is
>completed successfully. But still the process is not killed and it stays in
>this mode for indefinite time.
>How can I kill such kind of process? I even tried re-starting
>SQLServeragent, but it didn't help. Anybody is facing such problems?
>Thanks
>GYK

killing a user process

Hi,
I have a user process related to sql mail (xp_readmail) which was submitted.
It was taking a longtime to execute, hence I tried to kill this process using
KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
again execute the KILL command WITH_STATUS_ONLY, it says rollback is
completed successfully. But still the process is not killed and it stays in
this mode for indefinite time.
How can I kill such kind of process? I even tried re-starting
SQLServeragent, but it didn't help. Anybody is facing such problems?
Thanks
GYK
It's running mapi and you may need to use the command line
kill to kill the mapi32 OS process - or use task manager and
kill the processes.
-Sue
On Tue, 28 Sep 2004 12:15:03 -0700, GYK
<GYK@.discussions.microsoft.com> wrote:

>Hi,
>I have a user process related to sql mail (xp_readmail) which was submitted.
>It was taking a longtime to execute, hence I tried to kill this process using
>KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
>again execute the KILL command WITH_STATUS_ONLY, it says rollback is
>completed successfully. But still the process is not killed and it stays in
>this mode for indefinite time.
>How can I kill such kind of process? I even tried re-starting
>SQLServeragent, but it didn't help. Anybody is facing such problems?
>Thanks
>GYK

killing a thread

Hey all,
Just wondering if there is any way to kill a thread within an sqlerver process. The thread we are trying to kill is a rollback statement that has been running for a very long time.
Any ideas ?
Thanks in advance,
KilkaC4 or nitroglycerin ?

I can't think of any reason to ever kill a rollback, other than to shutdown the server so that recovery can do the rollback faster when it has exclusive use of the database. Anything that prevents a rollback from completing essentially permanently corrupts the database.

-PatP|||Is it a result of a previous kill?|||yeah. It's the result of a previous kill.

Currently, I get this error message when I try and kill the spid.

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

The rollback has been running for a couple hours now. Does anyone know if there is any way to see what it is rolling back ?

I'm going to bounce the server and see what happens.|||ok, so it appears the rollback was just hanging there. A bounce was all that was required. I'm still not sure if it's possible to tell what the rollback is working on. I think it would usefull to know. The reason I ask is because the spid is the result of another app working on the surface and it's quite difficult to tell what the app was doing at the time...

Cheers,
-Kilka|||You sure you're not Darkwing Duck?|||The rollback in 9 out of 10 will take longer (in many cases much longer) than the original transaction. Rollback is impossible to kill with a KILL command. The only way you can undo what KILL does is by bouncing the server, which you've already done. When the database gets recovered during the recovery process, the original rollback attempts to dismiss any originating transaction (basically ignoring the altered data pages recorded in the transaction log) and just moves on with what was actually committed and recorded in the trx log..|||Maybe there's some undocumented way of doing it? I'm in the situation right now that I'm waiting for a rollback that will take hours, and I just want to re-create the database from an old backup anyway. However, there are production databases on the same server so I can't shut down anything outside the particular database.sql

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIA
Hi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>
|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi Dan,
I wrote the following script and tested it:
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIAHi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi Dan,
I wrote the following script and tested it:
--
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
--
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

killing a process shows 0% completion time

Hello,
I'm running Sql server 2005 and I've noticed that when I kill a
process, it always shows
"Estimated rollback completion: 0%. Estimated time remaining: 0
seconds."
Even though the process does kill successfully, these numbers never
change. Is there some setting I have to change or what?
Can anyone help?
Thanks.When you kill a running process/transaction, the system will have to go
through and rollback the transaction. There is nothing you can do here other
than to wait for completion.
If you force a system restart, the transaction will be re-rollbacked on the
next restart.
--
-oj
<clemlau@.yahoo.com> wrote in message
news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> Hello,
> I'm running Sql server 2005 and I've noticed that when I kill a
> process, it always shows
>
> "Estimated rollback completion: 0%. Estimated time remaining: 0
> seconds."
>
> Even though the process does kill successfully, these numbers never
> change. Is there some setting I have to change or what?
> Can anyone help?
> Thanks.
>|||This is a multi-part message in MIME format.
--010208050704050705070309
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
How are you getting the completion figure? Are you using "KILL <spid>
WITH STATUSONLY"?
--
*mike hodgson*
http://sqlnerd.blogspot.com
clemlau@.yahoo.com wrote:
>Hello,
>I'm running Sql server 2005 and I've noticed that when I kill a
>process, it always shows
>
>"Estimated rollback completion: 0%. Estimated time remaining: 0
>seconds."
>
>Even though the process does kill successfully, these numbers never
>change. Is there some setting I have to change or what?
>Can anyone help?
>Thanks.
>
>
--010208050704050705070309
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>How are you getting the completion figure? Are you using "KILL
<spid> WITH STATUSONLY"?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:clemlau@.yahoo.com">clemlau@.yahoo.com</a> wrote:
<blockquote
cite="mid1151442931.224581.203680@.b68g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Hello,
I'm running Sql server 2005 and I've noticed that when I kill a
process, it always shows
"Estimated rollback completion: 0%. Estimated time remaining: 0
seconds."
Even though the process does kill successfully, these numbers never
change. Is there some setting I have to change or what?
Can anyone help?
Thanks.
</pre>
</blockquote>
</body>
</html>
--010208050704050705070309--|||And just to add to the doom and gloom, the rollback is part of the database
recovery. No connections to the database will be allowed until the rollback
is complete. I have seen (and survived with job intact) a four-hour unwind
on a restart, so this can get very bad.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> When you kill a running process/transaction, the system will have to go
> through and rollback the transaction. There is nothing you can do here
> other than to wait for completion.
> If you force a system restart, the transaction will be re-rollbacked on
> the next restart.
> --
> -oj
>
> <clemlau@.yahoo.com> wrote in message
> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> Hello,
>> I'm running Sql server 2005 and I've noticed that when I kill a
>> process, it always shows
>>
>> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> seconds."
>>
>> Even though the process does kill successfully, these numbers never
>> change. Is there some setting I have to change or what?
>> Can anyone help?
>> Thanks.
>|||it's sql2k5! ;-)
the db should be avail as soons as redo is done.
--
-oj
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> And just to add to the doom and gloom, the rollback is part of the
> database recovery. No connections to the database will be allowed until
> the rollback is complete. I have seen (and survived with job intact) a
> four-hour unwind on a restart, so this can get very bad.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>> When you kill a running process/transaction, the system will have to go
>> through and rollback the transaction. There is nothing you can do here
>> other than to wait for completion.
>> If you force a system restart, the transaction will be re-rollbacked on
>> the next restart.
>> --
>> -oj
>>
>> <clemlau@.yahoo.com> wrote in message
>> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> Hello,
>> I'm running Sql server 2005 and I've noticed that when I kill a
>> process, it always shows
>>
>> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> seconds."
>>
>> Even though the process does kill successfully, these numbers never
>> change. Is there some setting I have to change or what?
>> Can anyone help?
>> Thanks.
>>
>|||You are correct.
Good catch. Thanks,
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> it's sql2k5! ;-)
> the db should be avail as soons as redo is done.
> --
> -oj
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
>> And just to add to the doom and gloom, the rollback is part of the
>> database recovery. No connections to the database will be allowed until
>> the rollback is complete. I have seen (and survived with job intact) a
>> four-hour unwind on a restart, so this can get very bad.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "oj" <nospam_ojngo@.home.com> wrote in message
>> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>> When you kill a running process/transaction, the system will have to go
>> through and rollback the transaction. There is nothing you can do here
>> other than to wait for completion.
>> If you force a system restart, the transaction will be re-rollbacked on
>> the next restart.
>> --
>> -oj
>>
>> <clemlau@.yahoo.com> wrote in message
>> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> Hello,
>> I'm running Sql server 2005 and I've noticed that when I kill a
>> process, it always shows
>>
>> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> seconds."
>>
>> Even though the process does kill successfully, these numbers never
>> change. Is there some setting I have to change or what?
>> Can anyone help?
>> Thanks.
>>
>>
>|||I'm running kill spid with statusonly. This result always shows 0%
completion. Whether it takes 5 seconds or 8 hours to rollback, I
always see 0% completion. (I had a process today that I had to kill
after running for 6 hours and it took 8 hours to kill but I had no idea
about it's progress.)
In sql 2000, this worked everytime I killed a process. I could see the
% changing and the estimated time to complete changing.
Any ideas?
Thanks,
Clem
Geoff N. Hiten wrote:
> You are correct.
> Good catch. Thanks,
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> > it's sql2k5! ;-)
> >
> > the db should be avail as soons as redo is done.
> >
> > --
> > -oj
> >
> >
> >
> > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> > news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> >> And just to add to the doom and gloom, the rollback is part of the
> >> database recovery. No connections to the database will be allowed until
> >> the rollback is complete. I have seen (and survived with job intact) a
> >> four-hour unwind on a restart, so this can get very bad.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >>
> >>
> >> "oj" <nospam_ojngo@.home.com> wrote in message
> >> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> >> When you kill a running process/transaction, the system will have to go
> >> through and rollback the transaction. There is nothing you can do here
> >> other than to wait for completion.
> >>
> >> If you force a system restart, the transaction will be re-rollbacked on
> >> the next restart.
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> <clemlau@.yahoo.com> wrote in message
> >> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> >> Hello,
> >>
> >> I'm running Sql server 2005 and I've noticed that when I kill a
> >> process, it always shows
> >>
> >>
> >> "Estimated rollback completion: 0%. Estimated time remaining: 0
> >> seconds."
> >>
> >>
> >> Even though the process does kill successfully, these numbers never
> >> change. Is there some setting I have to change or what?
> >>
> >> Can anyone help?
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >>
> >
> >|||> the db should be avail as soons as redo is done.
On Enterprise and Developer Edition... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"oj" <nospam_ojngo@.home.com> wrote in message news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> it's sql2k5! ;-)
> the db should be avail as soons as redo is done.
> --
> -oj
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
>> And just to add to the doom and gloom, the rollback is part of the
>> database recovery. No connections to the database will be allowed until
>> the rollback is complete. I have seen (and survived with job intact) a
>> four-hour unwind on a restart, so this can get very bad.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "oj" <nospam_ojngo@.home.com> wrote in message
>> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>> When you kill a running process/transaction, the system will have to go
>> through and rollback the transaction. There is nothing you can do here
>> other than to wait for completion.
>> If you force a system restart, the transaction will be re-rollbacked on
>> the next restart.
>> --
>> -oj
>>
>> <clemlau@.yahoo.com> wrote in message
>> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> Hello,
>> I'm running Sql server 2005 and I've noticed that when I kill a
>> process, it always shows
>>
>> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> seconds."
>>
>> Even though the process does kill successfully, these numbers never
>> change. Is there some setting I have to change or what?
>> Can anyone help?
>> Thanks.
>>
>>
>|||that's your problem. you're not actually killing the spid.
"WITH STATUSONLY
Generates a progress report on a given spid or UOW that is being rolled
back due to an earlier KILL statement. KILL WITH STATUSONLY does not
terminate or roll back the spid or UOW, it only displays the current
progress of the roll back."
-oj
<clemlau@.yahoo.com> wrote in message
news:1151467451.774578.30370@.i40g2000cwc.googlegroups.com...
>
> I'm running kill spid with statusonly. This result always shows 0%
> completion. Whether it takes 5 seconds or 8 hours to rollback, I
> always see 0% completion. (I had a process today that I had to kill
> after running for 6 hours and it took 8 hours to kill but I had no idea
> about it's progress.)
> In sql 2000, this worked everytime I killed a process. I could see the
> % changing and the estimated time to complete changing.
> Any ideas?
>
> Thanks,
> Clem
>
> Geoff N. Hiten wrote:
>> You are correct.
>> Good catch. Thanks,
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "oj" <nospam_ojngo@.home.com> wrote in message
>> news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
>> > it's sql2k5! ;-)
>> >
>> > the db should be avail as soons as redo is done.
>> >
>> > --
>> > -oj
>> >
>> >
>> >
>> > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> > news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
>> >> And just to add to the doom and gloom, the rollback is part of the
>> >> database recovery. No connections to the database will be allowed
>> >> until
>> >> the rollback is complete. I have seen (and survived with job intact)
>> >> a
>> >> four-hour unwind on a restart, so this can get very bad.
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >>
>> >>
>> >>
>> >> "oj" <nospam_ojngo@.home.com> wrote in message
>> >> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>> >> When you kill a running process/transaction, the system will have to
>> >> go
>> >> through and rollback the transaction. There is nothing you can do
>> >> here
>> >> other than to wait for completion.
>> >>
>> >> If you force a system restart, the transaction will be re-rollbacked
>> >> on
>> >> the next restart.
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> <clemlau@.yahoo.com> wrote in message
>> >> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> >> Hello,
>> >>
>> >> I'm running Sql server 2005 and I've noticed that when I kill a
>> >> process, it always shows
>> >>
>> >>
>> >> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> >> seconds."
>> >>
>> >>
>> >> Even though the process does kill successfully, these numbers never
>> >> change. Is there some setting I have to change or what?
>> >>
>> >> Can anyone help?
>> >>
>> >> Thanks.
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>|||so true. ;-)
--
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23NQm%238nmGHA.4212@.TK2MSFTNGP03.phx.gbl...
>> the db should be avail as soons as redo is done.
> On Enterprise and Developer Edition... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This is a multi-part message in MIME format.
--060106070200070902040203
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I think you're missing the point - the OP has already issued a KILL
statement against the SPID in question and then, after that, he runs
"KILL <spid> WITH STATUSONLY" to see how the rollback is going, but the
figure that gets reported is always 0% on the rollback. [Is that right,
Clem?]
While I haven't analysed the situation much in SQL 2005, I have seen
similar behaviour. It made me curious at the time, but not enough to
find out what was going on, given that, on our SQL 2005 box, we don't
kill much (at least not yet). Sorry to be not much help at this time.
--
*mike hodgson*
http://sqlnerd.blogspot.com
oj wrote:
>that's your problem. you're not actually killing the spid.
>"WITH STATUSONLY
> Generates a progress report on a given spid or UOW that is being rolled
>back due to an earlier KILL statement. KILL WITH STATUSONLY does not
>terminate or roll back the spid or UOW, it only displays the current
>progress of the roll back."
>
>
--060106070200070902040203
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I think you're missing the point - the OP has already issued a KILL
statement against the SPID in question and then, after that, he runs
"KILL <spid> WITH STATUSONLY" to see how the rollback is going,
but the figure that gets reported is always 0% on the rollback. [Is
that right, Clem?]<br>
<br>
While I haven't analysed the situation much in SQL 2005, I have seen
similar behaviour. It made me curious at the time, but not enough to
find out what was going on, given that, on our SQL 2005 box, we don't
kill much (at least not yet). Sorry to be not much help at this time.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
oj wrote:
<blockquote cite="mid%23c$BJRomGHA.2316@.TK2MSFTNGP04.phx.gbl"
type="cite">
<pre wrap="">that's your problem. you're not actually killing the spid.
"WITH STATUSONLY
Generates a progress report on a given spid or UOW that is being rolled
back due to an earlier KILL statement. KILL WITH STATUSONLY does not
terminate or roll back the spid or UOW, it only displays the current
progress of the roll back."
</pre>
</blockquote>
</body>
</html>
--060106070200070902040203--|||Yes you''re correct Mike. I just want to see the progress of the
rollback and it's always at 0%.
Mike Hodgson wrote:
> I think you're missing the point - the OP has already issued a KILL
> statement against the SPID in question and then, after that, he runs
> "KILL <spid> WITH STATUSONLY" to see how the rollback is going, but the
> figure that gets reported is always 0% on the rollback. [Is that right,
> Clem?]
> While I haven't analysed the situation much in SQL 2005, I have seen
> similar behaviour. It made me curious at the time, but not enough to
> find out what was going on, given that, on our SQL 2005 box, we don't
> kill much (at least not yet). Sorry to be not much help at this time.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> oj wrote:
> >that's your problem. you're not actually killing the spid.
> >
> >"WITH STATUSONLY
> > Generates a progress report on a given spid or UOW that is being rolled
> >back due to an earlier KILL statement. KILL WITH STATUSONLY does not
> >terminate or roll back the spid or UOW, it only displays the current
> >progress of the roll back."
> >
> >
> >
> >
> --060106070200070902040203
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 1688
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>I think you're missing the point - the OP has already issued a KILL
> statement against the SPID in question and then, after that, he runs
> "KILL <spid> WITH STATUSONLY" to see how the rollback is going,
> but the figure that gets reported is always 0% on the rollback. [Is
> that right, Clem?]<br>
> <br>
> While I haven't analysed the situation much in SQL 2005, I have seen
> similar behaviour. It made me curious at the time, but not enough to
> find out what was going on, given that, on our SQL 2005 box, we don't
> kill much (at least not yet). Sorry to be not much help at this time.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> oj wrote:
> <blockquote cite="mid%23c$BJRomGHA.2316@.TK2MSFTNGP04.phx.gbl"
> type="cite">
> <pre wrap="">that's your problem. you're not actually killing the spid.
> "WITH STATUSONLY
> Generates a progress report on a given spid or UOW that is being rolled
> back due to an earlier KILL statement. KILL WITH STATUSONLY does not
> terminate or roll back the spid or UOW, it only displays the current
> progress of the roll back."
>
> </pre>
> </blockquote>
> </body>
> </html>
> --060106070200070902040203--|||<clemlau@.yahoo.com> wrote in message
news:1151467451.774578.30370@.i40g2000cwc.googlegroups.com...
>
> I'm running kill spid with statusonly. This result always shows 0%
> completion. Whether it takes 5 seconds or 8 hours to rollback, I
> always see 0% completion. (I had a process today that I had to kill
> after running for 6 hours and it took 8 hours to kill but I had no idea
> about it's progress.)
> In sql 2000, this worked everytime I killed a process. I could see the
> % changing and the estimated time to complete changing.
Somethings just don't roll back nicely.
Especially anything calling an XP procedure or cross-database calls.
I've also seen a few cases (with SQL2000) where if the client disconnects
non-cleanly, the rollback may show 100% complete, but the SPID never goes
away.
> Any ideas?
>
> Thanks,
> Clem
>
> Geoff N. Hiten wrote:
> > You are correct.
> >
> > Good catch. Thanks,
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> > > it's sql2k5! ;-)
> > >
> > > the db should be avail as soons as redo is done.
> > >
> > > --
> > > -oj
> > >
> > >
> > >
> > > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> > > news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> > >> And just to add to the doom and gloom, the rollback is part of the
> > >> database recovery. No connections to the database will be allowed
until
> > >> the rollback is complete. I have seen (and survived with job intact)
a
> > >> four-hour unwind on a restart, so this can get very bad.
> > >>
> > >> --
> > >> Geoff N. Hiten
> > >> Senior Database Administrator
> > >> Microsoft SQL Server MVP
> > >>
> > >>
> > >>
> > >> "oj" <nospam_ojngo@.home.com> wrote in message
> > >> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> > >> When you kill a running process/transaction, the system will have to
go
> > >> through and rollback the transaction. There is nothing you can do
here
> > >> other than to wait for completion.
> > >>
> > >> If you force a system restart, the transaction will be re-rollbacked
on
> > >> the next restart.
> > >>
> > >> --
> > >> -oj
> > >>
> > >>
> > >>
> > >> <clemlau@.yahoo.com> wrote in message
> > >> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> > >> Hello,
> > >>
> > >> I'm running Sql server 2005 and I've noticed that when I kill a
> > >> process, it always shows
> > >>
> > >>
> > >> "Estimated rollback completion: 0%. Estimated time remaining: 0
> > >> seconds."
> > >>
> > >>
> > >> Even though the process does kill successfully, these numbers
never
> > >> change. Is there some setting I have to change or what?
> > >>
> > >> Can anyone help?
> > >>
> > >> Thanks.
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
>

killing a process shows 0% completion time

When you kill a running process/transaction, the system will have to go
through and rollback the transaction. There is nothing you can do here other
than to wait for completion.
If you force a system restart, the transaction will be re-rollbacked on the
next restart.
-oj
<clemlau@.yahoo.com> wrote in message
news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> Hello,
> I'm running Sql server 2005 and I've noticed that when I kill a
> process, it always shows
>
> "Estimated rollback completion: 0%. Estimated time remaining: 0
> seconds."
>
> Even though the process does kill successfully, these numbers never
> change. Is there some setting I have to change or what?
> Can anyone help?
> Thanks.
>How are you getting the completion figure? Are you using "KILL <spid>
WITH STATUSONLY"?
*mike hodgson*
http://sqlnerd.blogspot.com
clemlau@.yahoo.com wrote:

>Hello,
>I'm running Sql server 2005 and I've noticed that when I kill a
>process, it always shows
>
>"Estimated rollback completion: 0%. Estimated time remaining: 0
>seconds."
>
>Even though the process does kill successfully, these numbers never
>change. Is there some setting I have to change or what?
>Can anyone help?
>Thanks.
>
>|||And just to add to the doom and gloom, the rollback is part of the database
recovery. No connections to the database will be allowed until the rollback
is complete. I have seen (and survived with job intact) a four-hour unwind
on a restart, so this can get very bad.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> When you kill a running process/transaction, the system will have to go
> through and rollback the transaction. There is nothing you can do here
> other than to wait for completion.
> If you force a system restart, the transaction will be re-rollbacked on
> the next restart.
> --
> -oj
>
> <clemlau@.yahoo.com> wrote in message
> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>|||it's sql2k5! ;-)
the db should be avail as soons as redo is done.
-oj
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> And just to add to the doom and gloom, the rollback is part of the
> database recovery. No connections to the database will be allowed until
> the rollback is complete. I have seen (and survived with job intact) a
> four-hour unwind on a restart, so this can get very bad.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>|||Hello,
I'm running Sql server 2005 and I've noticed that when I kill a
process, it always shows
"Estimated rollback completion: 0%. Estimated time remaining: 0
seconds."
Even though the process does kill successfully, these numbers never
change. Is there some setting I have to change or what?
Can anyone help?
Thanks.|||When you kill a running process/transaction, the system will have to go
through and rollback the transaction. There is nothing you can do here other
than to wait for completion.
If you force a system restart, the transaction will be re-rollbacked on the
next restart.
-oj
<clemlau@.yahoo.com> wrote in message
news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> Hello,
> I'm running Sql server 2005 and I've noticed that when I kill a
> process, it always shows
>
> "Estimated rollback completion: 0%. Estimated time remaining: 0
> seconds."
>
> Even though the process does kill successfully, these numbers never
> change. Is there some setting I have to change or what?
> Can anyone help?
> Thanks.
>|||How are you getting the completion figure? Are you using "KILL <spid>
WITH STATUSONLY"?
*mike hodgson*
http://sqlnerd.blogspot.com
clemlau@.yahoo.com wrote:

>Hello,
>I'm running Sql server 2005 and I've noticed that when I kill a
>process, it always shows
>
>"Estimated rollback completion: 0%. Estimated time remaining: 0
>seconds."
>
>Even though the process does kill successfully, these numbers never
>change. Is there some setting I have to change or what?
>Can anyone help?
>Thanks.
>
>|||And just to add to the doom and gloom, the rollback is part of the database
recovery. No connections to the database will be allowed until the rollback
is complete. I have seen (and survived with job intact) a four-hour unwind
on a restart, so this can get very bad.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> When you kill a running process/transaction, the system will have to go
> through and rollback the transaction. There is nothing you can do here
> other than to wait for completion.
> If you force a system restart, the transaction will be re-rollbacked on
> the next restart.
> --
> -oj
>
> <clemlau@.yahoo.com> wrote in message
> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>|||You are correct.
Good catch. Thanks,
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> it's sql2k5! ;-)
> the db should be avail as soons as redo is done.
> --
> -oj
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
>|||I'm running kill spid with statusonly. This result always shows 0%
completion. Whether it takes 5 seconds or 8 hours to rollback, I
always see 0% completion. (I had a process today that I had to kill
after running for 6 hours and it took 8 hours to kill but I had no idea
about it's progress.)
In sql 2000, this worked everytime I killed a process. I could see the
% changing and the estimated time to complete changing.
Any ideas?
Thanks,
Clem
Geoff N. Hiten wrote:[vbcol=seagreen]
> You are correct.
> Good catch. Thanks,
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...

Killing a process does not help table that can't be read

I have a table that is locked by a process, but when I try to kill it using
Query Analyzer, I get the following message:
"SPID 136: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds."
It has been saying this for a long time now and I need to get this table
functioning because it's my main customer table. Any help is appreciated.
I had a similar problem yesterday with a view yesterday, and I couldn't get
it cleared out until I restarted the server. It doesn't make sense to me
that I would have to restart an entire server to unlock this one table, does
it?
Jeremiah
Hi
It looks like people have contacted PSS regarding this in the past!
http://tinyurl.com/5opp6
You may want to check your version number and see if there are any more
up-to-date patches that fix it.
John
"Jeremiah Traxler" wrote:

> I have a table that is locked by a process, but when I try to kill it using
> Query Analyzer, I get the following message:
> "SPID 136: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds."
> It has been saying this for a long time now and I need to get this table
> functioning because it's my main customer table. Any help is appreciated.
> I had a similar problem yesterday with a view yesterday, and I couldn't get
> it cleared out until I restarted the server. It doesn't make sense to me
> that I would have to restart an entire server to unlock this one table, does
> it?
> Jeremiah
sql

Killing a process does not help table that can't be read

I have a table that is locked by a process, but when I try to kill it using
Query Analyzer, I get the following message:
"SPID 136: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds."
It has been saying this for a long time now and I need to get this table
functioning because it's my main customer table. Any help is appreciated.
I had a similar problem yesterday with a view yesterday, and I couldn't get
it cleared out until I restarted the server. It doesn't make sense to me
that I would have to restart an entire server to unlock this one table, does
it?
JeremiahHi
It looks like people have contacted PSS regarding this in the past!
http://tinyurl.com/5opp6
You may want to check your version number and see if there are any more
up-to-date patches that fix it.
John
"Jeremiah Traxler" wrote:

> I have a table that is locked by a process, but when I try to kill it usin
g
> Query Analyzer, I get the following message:
> "SPID 136: transaction rollback in progress. Estimated rollback completion
:
> 0%. Estimated time remaining: 0 seconds."
> It has been saying this for a long time now and I need to get this table
> functioning because it's my main customer table. Any help is appreciated.
> I had a similar problem yesterday with a view yesterday, and I couldn't ge
t
> it cleared out until I restarted the server. It doesn't make sense to me
> that I would have to restart an entire server to unlock this one table, do
es
> it?
> Jeremiah