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 mupltiple batches

Is there a SQL command which will kill or exit all batches in the query
analyzer?
For example I have this script:
query1
query2
GO
query3
query4
query5
GO
Is there a way so that when I check @.@.ERROR after query1 that query2,3,4,5
do NOT get executed? GOTO's cannot see beyond the next GO.
Thanks,
Greg
There is no neat way to do this in Query Analyzer except raising an error
with severity 20 or higher, which will terminate your connection:
RAISERROR ('Your message here.', 20, 1)
If you use osql to run a script, you can raise an error with a status of
127, which will have the same effect, but won't leave traces in the SQL
Server error log like raising an error with status 20 does.
RAISERROR ('Your message here.', 16, 127)
Jacco Schalkwijk
SQL Server MVP
"Greg Michalopoulos" <gmichalopoulos@.d2hawkeye.com> wrote in message
news:OYM9dqG5EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Is there a SQL command which will kill or exit all batches in the query
> analyzer?
> For example I have this script:
> query1
> query2
> GO
> query3
> query4
> query5
> GO
> Is there a way so that when I check @.@.ERROR after query1 that query2,3,4,5
> do NOT get executed? GOTO's cannot see beyond the next GO.
> Thanks,
> Greg
>
|||Hello I had a similar problem as original poster - needing to kill multiple
batches within the same script.
Raiserror is not working for me.
raiserror ('just kill me now',19,1, 'WITH LOG,NOWAIT')
comes back with
Server: Msg 2754, Level 16, State 1, Line 1
Error severity levels greater than 18 can only be specified by members of
the sysadmin role, using the WITH LOG option.
I am sure that the account i am using has the sysadmin fixed server role.
PLEASE HELP!
Thanks,
Joel Mariano
"Jacco Schalkwijk" wrote:

> There is no neat way to do this in Query Analyzer except raising an error
> with severity 20 or higher, which will terminate your connection:
> RAISERROR ('Your message here.', 20, 1)
> If you use osql to run a script, you can raise an error with a status of
> 127, which will have the same effect, but won't leave traces in the SQL
> Server error log like raising an error with status 20 does.
> RAISERROR ('Your message here.', 16, 127)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Greg Michalopoulos" <gmichalopoulos@.d2hawkeye.com> wrote in message
> news:OYM9dqG5EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>
|||Whoops, brain fart:
raiserror ('just kill me now',20,1) WITH LOG,NOWAIT
works just fine (drops the connection).
"Joel Mariano" wrote:
[vbcol=seagreen]
> Hello I had a similar problem as original poster - needing to kill multiple
> batches within the same script.
> Raiserror is not working for me.
> raiserror ('just kill me now',19,1, 'WITH LOG,NOWAIT')
> comes back with
> Server: Msg 2754, Level 16, State 1, Line 1
> Error severity levels greater than 18 can only be specified by members of
> the sysadmin role, using the WITH LOG option.
> I am sure that the account i am using has the sysadmin fixed server role.
> PLEASE HELP!
> Thanks,
> Joel Mariano
> "Jacco Schalkwijk" wrote:
sql

KILL spid never ends (SQL 2000)

Andrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I c
an
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?Dear all,
From time to time an SQL process ('spid') hangs up in our production SQL
server.
Then we try to kill it using KILL command.
And quite often the KILL never ends.
If I call (spid to kill is 160):
KILL 160 WITH STATUSONLY
the result is:
SPID 160: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
and that result lasts forever (i.e. a couple of days until we restart the
server).
That is quite troublesome as the server is quite heavily used and restarting
it always interferes with normal work of users.
Is there any way to REALLY kill the process?
I guess my problems are related to locking issues, I looked up the
newsgroups but couldn't figure out any feasible solution but restart the
server.
To be more specific, my current problem is as follows:
A developer intended to alter a view in a production database. They issued
an ALTER VIEW command and, because of some reason, the process hung up.
In the EM, if I go to Management --> Current Activity --> Process Info I can
see that process as spid 160, the statement is "ALTER VIEW..."
I killed the process, as described above, but it is still alive.
If I call
select * from sysprocesses where spid = 160
the result is:
spid 160
kpid 1320
blocked 0
waittype 0x0000
waittime 0
lastwaittype OLEDB
waitresource
dbid 17
uid 0
cpu 21265
physical_io 2299
memusage 476
login_time 2006-06-23 08:34:36.450
last_batch 2006-06-23 09:39:55.577
ecid 0
open_tran 2
status runnable
sid 0x010500000000000515000000AA562D4A3E3CC3
6A4B1A1304ED030...0
hostname XXX
program_name MS SQLEM
hostprocess 3900
cmd AWAITING COMMAND
nt_domain XXX
nt_username XXX
net_address XXX
net_library TCP/IP
loginame XXX\XXX
context_info 0x0...0
sql_handle 0x0...0
stmt_start 0
stmt_end 0
Now, if I go to Locks / Process ID I can see that process 160 is marked as
'Blocking'.
Moreover, there are many processes that are marked as 'Blocked by 160'.
The result of
sp_lock '160'
is:
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
160 17 0 0 DB S GRANT
160 17 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 52 0 0 DB S GRANT
160 47 0 0 DB S GRANT
160 17 804197915 0 TAB Sch-S GRANT
160 17 804197915 0 TAB [COMPILE] X GRANT
160 47 1990402260 0 TAB Sch-S GRANT
160 8 985106600 0 TAB Sch-S GRANT
160 17 1767677345 0 TAB Sch-S GRANT
160 52 1893581784 0 TAB Sch-S GRANT
160 8 793105916 0 TAB [COMPILE] X GRANT
160 8 793105916 0 TAB Sch-S GRANT
160 52 1285579618 0 TAB Sch-S GRANT
And that is all what I was able to check.
What can I do in that situation? Is the server restart really necessary?
Probably, I will end up restarting the SQL this time, but I would like to
learn what to do if that happens again.
Any help / ideas / suggestions would be greatly appreciated.
Thank you in advance!
Best regards,
Andrew|||Andrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I c
an
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Yes, it does. Actually it is a bit more complicated, because the view refe
rs
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Yes, it does. Actually it is a bit more complicated, because the view refe
rs
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||Andrew Drake wrote:
> Thank you very much for your help!
> Best regards,
> Andrew
>
No problem... FYI, the same thing can happen to a process that uses
xp_cmdshell to run an OS command, like a COPY command to copy a large
backup file. Killing the spid will exhibit the same behavior that you
saw, to completely kill it you have to open the Windows Task Manager and
kill the CMD.EXE process that was spawned by xp_cmdshell.

KILL spid never ends (SQL 2000)

Dear all,
From time to time an SQL process ('spid') hangs up in our production SQL
server.
Then we try to kill it using KILL command.
And quite often the KILL never ends.
If I call (spid to kill is 160):
KILL 160 WITH STATUSONLY
the result is:
SPID 160: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
and that result lasts forever (i.e. a couple of days until we restart the
server).
That is quite troublesome as the server is quite heavily used and restarting
it always interferes with normal work of users.
Is there any way to REALLY kill the process?
I guess my problems are related to locking issues, I looked up the
newsgroups but couldn't figure out any feasible solution but restart the
server.
To be more specific, my current problem is as follows:
A developer intended to alter a view in a production database. They issued
an ALTER VIEW command and, because of some reason, the process hung up.
In the EM, if I go to Management --> Current Activity --> Process Info I can
see that process as spid 160, the statement is "ALTER VIEW..."
I killed the process, as described above, but it is still alive.
If I call
select * from sysprocesses where spid = 160
the result is:
spid 160
kpid 1320
blocked 0
waittype 0x0000
waittime 0
lastwaittype OLEDB
waitresource
dbid 17
uid 0
cpu 21265
physical_io 2299
memusage 476
login_time 2006-06-23 08:34:36.450
last_batch 2006-06-23 09:39:55.577
ecid 0
open_tran 2
status runnable
sid 0x010500000000000515000000AA562D4A3E3CC36A4B1A1304ED030...0
hostname XXX
program_name MS SQLEM
hostprocess 3900
cmd AWAITING COMMAND
nt_domain XXX
nt_username XXX
net_address XXX
net_library TCP/IP
loginame XXX\XXX
context_info 0x0...0
sql_handle 0x0...0
stmt_start 0
stmt_end 0
Now, if I go to Locks / Process ID I can see that process 160 is marked as
'Blocking'.
Moreover, there are many processes that are marked as 'Blocked by 160'.
The result of
sp_lock '160'
is:
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
160 17 0 0 DB S GRANT
160 17 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 52 0 0 DB S GRANT
160 47 0 0 DB S GRANT
160 17 804197915 0 TAB Sch-S GRANT
160 17 804197915 0 TAB [COMPILE] X GRANT
160 47 1990402260 0 TAB Sch-S GRANT
160 8 985106600 0 TAB Sch-S GRANT
160 17 1767677345 0 TAB Sch-S GRANT
160 52 1893581784 0 TAB Sch-S GRANT
160 8 793105916 0 TAB [COMPILE] X GRANT
160 8 793105916 0 TAB Sch-S GRANT
160 52 1285579618 0 TAB Sch-S GRANT
And that is all what I was able to check.
What can I do in that situation? Is the server restart really necessary?
Probably, I will end up restarting the SQL this time, but I would like to
learn what to do if that happens again.
Any help / ideas / suggestions would be greatly appreciated.
Thank you in advance!
Best regards,
AndrewAndrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I can
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info I
>> can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
>> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info I
>> can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
>> Does the view refer to any linked servers? Can you post the query that
>> makes up the view?
> Yes, it does. Actually it is a bit more complicated, because the view refers
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
>> Andrew Drake wrote:
>> Dear all,
>> A developer intended to alter a view in a production database. They
>> issued
>> an ALTER VIEW command and, because of some reason, the process hung up.
>> In the EM, if I go to Management --> Current Activity --> Process Info
>> I can
>> see that process as spid 160, the statement is "ALTER VIEW..."
>> Any help / ideas / suggestions would be greatly appreciated.
>> Does the view refer to any linked servers? Can you post the query that
>> makes up the view?
>> Yes, it does. Actually it is a bit more complicated, because the view
>> refers to another view in some other database, that in turn refers to a
>> linked server:
>> ALTER VIEW DB1.dbo.MyView
>> AS
>> SELECT T1.Field1, V2.Field2
>> FROM DB1.dbo.Table1 T1
>> JOIN DB2.dbo.View2 V2
>> ON T1.Field1 = V2.FieldX
>> where DB2.dbo.View2 refers to a linked server.
>> Does it matter that a linked server is involved?
>> Best regards,
>> Andrew
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||Andrew Drake wrote:
> Thank you very much for your help!
> Best regards,
> Andrew
>
No problem... FYI, the same thing can happen to a process that uses
xp_cmdshell to run an OS command, like a COPY command to copy a large
backup file. Killing the spid will exhibit the same behavior that you
saw, to completely kill it you have to open the Windows Task Manager and
kill the CMD.EXE process that was spawned by xp_cmdshell.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:eHoNt7qmGHA.2372@.TK2MSFTNGP04.phx.gbl...
> Andrew Drake wrote:
>> Thank you very much for your help!
>> Best regards,
>> Andrew
>>
> No problem... FYI, the same thing can happen to a process that uses
> xp_cmdshell to run an OS command, like a COPY command to copy a large
> backup file. Killing the spid will exhibit the same behavior that you
> saw, to completely kill it you have to open the Windows Task Manager and
> kill the CMD.EXE process that was spawned by xp_cmdshell.
Yes, you are right, we have experienced a similar behaviour with jobs that
contained an EXEC master.dbo.xp_cmdshell 'some.exe' command.
Stopping the job was not enough, we had to explicitly kill the exe process
at the OS level.
Thanks a lot again!
Best regards,
Andrew|||Hi, try to restart the MSDTC services..
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> > news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> >> Andrew Drake wrote:
> >> Dear all,
> >>
> >> A developer intended to alter a view in a production database. They
> >> issued
> >> an ALTER VIEW command and, because of some reason, the process hung
up.
> >> In the EM, if I go to Management --> Current Activity --> Process Info
I
> >> can
> >> see that process as spid 160, the statement is "ALTER VIEW..."
> >>
> >> Any help / ideas / suggestions would be greatly appreciated.
> >>
> >> Does the view refer to any linked servers? Can you post the query that
> >> makes up the view?
> >
> > Yes, it does. Actually it is a bit more complicated, because the view
refers
> > to another view in some other database, that in turn refers to a linked
> > server:
> >
> > ALTER VIEW DB1.dbo.MyView
> > AS
> > SELECT T1.Field1, V2.Field2
> > FROM DB1.dbo.Table1 T1
> > JOIN DB2.dbo.View2 V2
> > ON T1.Field1 = V2.FieldX
> >
> > where DB2.dbo.View2 refers to a linked server.
> >
> > Does it matter that a linked server is involved?
> >
> > Best regards,
> > Andrew
> >
> >
> >
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die
> as well.

Kill SPID Minimum SQL Server Privileges

What are the minimum SQL Server 2000 security privileges that a user needs
to use
the KILL command within SQL Server?
Thanks,This is a multi-part message in MIME format.
--020305000807010203020401
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
KILL permissions default to the members of the *sysadmin* and
*processadmin* fixed database roles, and are not transferable.
See BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Joe K. wrote:
>What are the minimum SQL Server 2000 security privileges that a user needs
>to use
>the KILL command within SQL Server?
>Thanks,
>
--020305000807010203020401
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>KILL permissions default to the members of the <b>sysadmin</b> and
<b>processadmin</b> fixed database roles, and are not transferable.<br>
<br>
See BOL:<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp</a><br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp</a><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"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Joe K. wrote:
<blockquote cite="midCA1BF761-8330-4FC4-831B-09BBE934FADB@.microsoft.com"
type="cite">
<pre wrap="">What are the minimum SQL Server 2000 security privileges that a user needs
to use
the KILL command within SQL Server?
Thanks,
</pre>
</blockquote>
</body>
</html>
--020305000807010203020401--sql

Kill SPID Minimum SQL Server Privileges

What are the minimum SQL Server 2000 security privileges that a user needs
to use
the KILL command within SQL Server?
Thanks,
KILL permissions default to the members of the *sysadmin* and
*processadmin* fixed database roles, and are not transferable.
See BOL:
http://msdn.microsoft.com/library/de...kf-kz_1zos.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Joe K. wrote:

>What are the minimum SQL Server 2000 security privileges that a user needs
>to use
>the KILL command within SQL Server?
>Thanks,
>

Kill SPID Minimum SQL Server Privileges

What are the minimum SQL Server 2000 security privileges that a user needs
to use
the KILL command within SQL Server?
Thanks,KILL permissions default to the members of the *sysadmin* and
*processadmin* fixed database roles, and are not transferable.
See BOL:
http://msdn.microsoft.com/library/d...br />
1zos.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Joe K. wrote:

>What are the minimum SQL Server 2000 security privileges that a user needs
>to use
>the KILL command within SQL Server?
>Thanks,
>

Kill Process

Is there any T-SQL language command to kill all process in one database?Hi
You have to loop thru all processes and generate KILL command
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:6FEB320A-307A-4B2B-926A-28EC1162BD2D@.microsoft.com...
> Is there any T-SQL language command to kill all process in one database?|||Try:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabase
SET MULTI_USER
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:6FEB320A-307A-4B2B-926A-28EC1162BD2D@.microsoft.com...
> Is there any T-SQL language command to kill all process in one database?

Kill Process

Hello again,
I have one process that appear runing (OMNIBACK), i've yet
kill the process (The command(s) completed
successfully)... but the process still running.
Can i do a SUPERKILL ;o)
i dont know what to do.
Best Regards> I have one process that appear runing (OMNIBACK), i've yet
> kill the process (The command(s) completed
> successfully)... but the process still running.
> Can i do a SUPERKILL ;o)
They are likely in the middle of a rollback. If you say KILL <spid> again
you should be able to see what percentage of rollback has completed.
To be safe, I would just let the process finish what it's doing to clean
itself up. Many people are tempted to just power down the box or force the
SQL Server service to restart, but I promise you this isn't the way to go...
can lead to suspect database, or a database that has to start the same
recovery/rollback process all over again before you will be able to use it.
While you're waiting, I would find out what they did, why you had to kill
the user, and take steps to prevent this from happening again.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||KILL is limited in its power.
The closest thing to SUPERKILL is the hefty: ALTER DATABASE mydb SET
SINGLE_USER. Then you can make it multi-user again. This will chase
_everybody_ (not one user) out of a database.
Russell Fields
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:ec0601c43cfd$7e94f420$a301280a@.phx.gbl...
> Hello again,
> I have one process that appear runing (OMNIBACK), i've yet
> kill the process (The command(s) completed
> successfully)... but the process still running.
> Can i do a SUPERKILL ;o)
> i dont know what to do.
> Best Regards
>|||you can add the status_only clause to Kill.
One question - if it's rolled back 100% but still not ending, is it running something outside of SQL? I.e. something like sp_OaCreate to run a COM component or xp_cmdshell or xp_sendmail?
If that's the case then SQL can lose sight of whether the process is active or not - normally because the dll (or whatever) hasn't reported status back.
Alici
http://www.sqlporn.co.uk|||Maybe it's executing a WSH script, and the server is waiting for someone to
walk by and click "OK" on a MsgBox. :-)
"Alicia" <anonymous@.discussions.microsoft.com> wrote in message
news:ABA285E4-A89A-45BE-BBB0-B2A6DCCEC86F@.microsoft.com...
> you can add the status_only clause to Kill.
> One question - if it's rolled back 100% but still not ending, is it
running something outside of SQL? I.e. something like sp_OaCreate to run a
COM component or xp_cmdshell or xp_sendmail?
> If that's the case then SQL can lose sight of whether the process is
active or not - normally because the dll (or whatever) hasn't reported
status back.
> Alicia
> http://www.sqlporn.co.uk|||Yeah - I've seen that in a job running DTS ActiveX script with debug code in
it...
xp_cmdshell 'notepad' will do it as well :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OS8lU6QPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> Maybe it's executing a WSH script, and the server is waiting for someone
to
> walk by and click "OK" on a MsgBox. :-)
>
>
> "Alicia" <anonymous@.discussions.microsoft.com> wrote in message
> news:ABA285E4-A89A-45BE-BBB0-B2A6DCCEC86F@.microsoft.com...
> > you can add the status_only clause to Kill.
> > One question - if it's rolled back 100% but still not ending, is it
> running something outside of SQL? I.e. something like sp_OaCreate to run a
> COM component or xp_cmdshell or xp_sendmail?
> > If that's the case then SQL can lose sight of whether the process is
> active or not - normally because the dll (or whatever) hasn't reported
> status back.
> >
> > Alicia
> >
> > http://www.sqlporn.co.uk
>sql

Kill Process

Is there any T-SQL language command to kill all process in one database?
Hi
You have to loop thru all processes and generate KILL command
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:6FEB320A-307A-4B2B-926A-28EC1162BD2D@.microsoft.com...
> Is there any T-SQL language command to kill all process in one database?
|||Try:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabase
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:6FEB320A-307A-4B2B-926A-28EC1162BD2D@.microsoft.com...
> Is there any T-SQL language command to kill all process in one database?

Kill Process

Hello again,
I have one process that appear runing (OMNIBACK), i've yet
kill the process (The command(s) completed
successfully)... but the process still running.
Can i do a SUPERKILL ;o)
i dont know what to do.
Best Regards
> I have one process that appear runing (OMNIBACK), i've yet
> kill the process (The command(s) completed
> successfully)... but the process still running.
> Can i do a SUPERKILL ;o)
They are likely in the middle of a rollback. If you say KILL <spid> again
you should be able to see what percentage of rollback has completed.
To be safe, I would just let the process finish what it's doing to clean
itself up. Many people are tempted to just power down the box or force the
SQL Server service to restart, but I promise you this isn't the way to go...
can lead to suspect database, or a database that has to start the same
recovery/rollback process all over again before you will be able to use it.
While you're waiting, I would find out what they did, why you had to kill
the user, and take steps to prevent this from happening again.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||KILL is limited in its power.
The closest thing to SUPERKILL is the hefty: ALTER DATABASE mydb SET
SINGLE_USER. Then you can make it multi-user again. This will chase
_everybody_ (not one user) out of a database.
Russell Fields
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:ec0601c43cfd$7e94f420$a301280a@.phx.gbl...
> Hello again,
> I have one process that appear runing (OMNIBACK), i've yet
> kill the process (The command(s) completed
> successfully)... but the process still running.
> Can i do a SUPERKILL ;o)
> i dont know what to do.
> Best Regards
>
|||you can add the status_only clause to Kill.
One question - if it's rolled back 100% but still not ending, is it running something outside of SQL? I.e. something like sp_OaCreate to run a COM component or xp_cmdshell or xp_sendmail?
If that's the case then SQL can lose sight of whether the process is active or not - normally because the dll (or whatever) hasn't reported status back.
Alicia
http://www.sqlporn.co.uk
|||Maybe it's executing a WSH script, and the server is waiting for someone to
walk by and click "OK" on a MsgBox. :-)
"Alicia" <anonymous@.discussions.microsoft.com> wrote in message
news:ABA285E4-A89A-45BE-BBB0-B2A6DCCEC86F@.microsoft.com...
> you can add the status_only clause to Kill.
> One question - if it's rolled back 100% but still not ending, is it
running something outside of SQL? I.e. something like sp_OaCreate to run a
COM component or xp_cmdshell or xp_sendmail?
> If that's the case then SQL can lose sight of whether the process is
active or not - normally because the dll (or whatever) hasn't reported
status back.
> Alicia
> http://www.sqlporn.co.uk
|||Yeah - I've seen that in a job running DTS ActiveX script with debug code in
it...
xp_cmdshell 'notepad' will do it as well :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OS8lU6QPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> Maybe it's executing a WSH script, and the server is waiting for someone
to
> walk by and click "OK" on a MsgBox. :-)
>
>
> "Alicia" <anonymous@.discussions.microsoft.com> wrote in message
> news:ABA285E4-A89A-45BE-BBB0-B2A6DCCEC86F@.microsoft.com...
> running something outside of SQL? I.e. something like sp_OaCreate to run a
> COM component or xp_cmdshell or xp_sendmail?
> active or not - normally because the dll (or whatever) hasn't reported
> status back.
>

Kill Process

Hello again,
I have one process that appear runing (OMNIBACK), i've yet
kill the process (The command(s) completed
successfully)... but the process still running.
Can i do a SUPERKILL ;o)
i dont know what to do.
Best Regards> I have one process that appear runing (OMNIBACK), i've yet
> kill the process (The command(s) completed
> successfully)... but the process still running.
> Can i do a SUPERKILL ;o)
They are likely in the middle of a rollback. If you say KILL <spid> again
you should be able to see what percentage of rollback has completed.
To be safe, I would just let the process finish what it's doing to clean
itself up. Many people are tempted to just power down the box or force the
SQL Server service to restart, but I promise you this isn't the way to go...
can lead to suspect database, or a database that has to start the same
recovery/rollback process all over again before you will be able to use it.
While you're waiting, I would find out what they did, why you had to kill
the user, and take steps to prevent this from happening again.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||KILL is limited in its power.
The closest thing to SUPERKILL is the hefty: ALTER DATABASE mydb SET
SINGLE_USER. Then you can make it multi-user again. This will chase
_everybody_ (not one user) out of a database.
Russell Fields
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:ec0601c43cfd$7e94f420$a301280a@.phx.gbl...
> Hello again,
> I have one process that appear runing (OMNIBACK), i've yet
> kill the process (The command(s) completed
> successfully)... but the process still running.
> Can i do a SUPERKILL ;o)
> i dont know what to do.
> Best Regards
>|||you can add the status_only clause to Kill.
One question - if it's rolled back 100% but still not ending, is it running
something outside of SQL? I.e. something like sp_OaCreate to run a COM compo
nent or xp_cmdshell or xp_sendmail?
If that's the case then SQL can lose sight of whether the process is active
or not - normally because the dll (or whatever) hasn't reported status back.
Alicia
http://www.sqlporn.co.uk|||Maybe it's executing a WSH script, and the server is waiting for someone to
walk by and click "OK" on a MsgBox. :-)
"Alicia" <anonymous@.discussions.microsoft.com> wrote in message
news:ABA285E4-A89A-45BE-BBB0-B2A6DCCEC86F@.microsoft.com...
> you can add the status_only clause to Kill.
> One question - if it's rolled back 100% but still not ending, is it
running something outside of SQL? I.e. something like sp_OaCreate to run a
COM component or xp_cmdshell or xp_sendmail?
> If that's the case then SQL can lose sight of whether the process is
active or not - normally because the dll (or whatever) hasn't reported
status back.
> Alicia
> http://www.sqlporn.co.uk|||Yeah - I've seen that in a job running DTS ActiveX script with debug code in
it...
xp_cmdshell 'notepad' will do it as well :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OS8lU6QPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> Maybe it's executing a WSH script, and the server is waiting for someone
to
> walk by and click "OK" on a MsgBox. :-)
>
>
> "Alicia" <anonymous@.discussions.microsoft.com> wrote in message
> news:ABA285E4-A89A-45BE-BBB0-B2A6DCCEC86F@.microsoft.com...
> running something outside of SQL? I.e. something like sp_OaCreate to run a
> COM component or xp_cmdshell or xp_sendmail?
> active or not - normally because the dll (or whatever) hasn't reported
> status back.
>

Kill Process

Is there any T-SQL language command to kill all process in one database?Hi
You have to loop thru all processes and generate KILL command
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:6FEB320A-307A-4B2B-926A-28EC1162BD2D@.microsoft.com...
> Is there any T-SQL language command to kill all process in one database?|||Try:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabase
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:6FEB320A-307A-4B2B-926A-28EC1162BD2D@.microsoft.com...
> Is there any T-SQL language command to kill all process in one database?

Kill multiple batches

Is there a SQL command which will kill or exit all batches in the query
analyzer?
For example I have this script:
query1
query2
GO
query3
query4
query5
GO
Is there a way so that when I check @.@.ERROR after query1 that query2,3,4,5
do NOT get executed? GOTO's cannot see beyond the next GO. RETURN just
jumps to right after the next GO as well.
Thanks,
Greg
Greg Michalopoulos wrote:
> Is there a SQL command which will kill or exit all batches in the
> query analyzer?
> For example I have this script:
> query1
> query2
> GO
> query3
> query4
> query5
> GO
> Is there a way so that when I check @.@.ERROR after query1 that
> query2,3,4,5 do NOT get executed? GOTO's cannot see beyond the next
> GO. RETURN just jumps to right after the next GO as well.
> Thanks,
> Greg
I assume you are running this from QA or equivalent? You can use a temp
table to store status if you need to.
David Gugick
Imceda Software
www.imceda.com

Kill DBCC Reindex?

Can anyone explain what happens if you kill a dbcc
reindex command (all indexes)?
DBCC DBREINDEX is one transaction, and when you kill a process, the
transactions that it is running will be rolled back. So you will end up with
the same situation you had before you ran DBCC DBREINDEX, and you will
probably have to wait quite a long time (maybe the same time as DBCC
DBREINDEX has run already) for the rollback to complete.
Jacco Schalkwijk
SQL Server MVP
"Mitch" <anonymous@.discussions.microsoft.com> wrote in message
news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
> Can anyone explain what happens if you kill a dbcc
> reindex command (all indexes)?
|||So if the process was blocking users to access a
database, and you killed the process, it just returns the
indexes back to their original form? Doesn't damage the
indexes?

>--Original Message--
>DBCC DBREINDEX is one transaction, and when you kill a
process, the
>transactions that it is running will be rolled back. So
you will end up with
>the same situation you had before you ran DBCC
DBREINDEX, and you will
>probably have to wait quite a long time (maybe the same
time as DBCC
>DBREINDEX has run already) for the rollback to complete.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Mitch" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
>
>.
>
|||It will return the indexes to what they were before. Your users can't access
the table though while the rollback is in progress.
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:5e4a01c474c5$c87f45e0$a401280a@.phx.gbl...[vbcol=seagreen]
> So if the process was blocking users to access a
> database, and you killed the process, it just returns the
> indexes back to their original form? Doesn't damage the
> indexes?
> process, the
> you will end up with
> DBREINDEX, and you will
> time as DBCC
> message

Kill DBCC Reindex?

Can anyone explain what happens if you kill a dbcc
reindex command (all indexes)?DBCC DBREINDEX is one transaction, and when you kill a process, the
transactions that it is running will be rolled back. So you will end up with
the same situation you had before you ran DBCC DBREINDEX, and you will
probably have to wait quite a long time (maybe the same time as DBCC
DBREINDEX has run already) for the rollback to complete.
Jacco Schalkwijk
SQL Server MVP
"Mitch" <anonymous@.discussions.microsoft.com> wrote in message
news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
> Can anyone explain what happens if you kill a dbcc
> reindex command (all indexes)?|||So if the process was blocking users to access a
database, and you killed the process, it just returns the
indexes back to their original form? Doesn't damage the
indexes?

>--Original Message--
>DBCC DBREINDEX is one transaction, and when you kill a
process, the
>transactions that it is running will be rolled back. So
you will end up with
>the same situation you had before you ran DBCC
DBREINDEX, and you will
>probably have to wait quite a long time (maybe the same
time as DBCC
>DBREINDEX has run already) for the rollback to complete.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Mitch" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
>
>.
>|||It will return the indexes to what they were before. Your users can't access
the table though while the rollback is in progress.
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:5e4a01c474c5$c87f45e0$a401280a@.phx.gbl...[vbcol=seagreen]
> So if the process was blocking users to access a
> database, and you killed the process, it just returns the
> indexes back to their original form? Doesn't damage the
> indexes?
>
> process, the
> you will end up with
> DBREINDEX, and you will
> time as DBCC
> messagesql

Kill DBCC Reindex?

Can anyone explain what happens if you kill a dbcc
reindex command (all indexes)?DBCC DBREINDEX is one transaction, and when you kill a process, the
transactions that it is running will be rolled back. So you will end up with
the same situation you had before you ran DBCC DBREINDEX, and you will
probably have to wait quite a long time (maybe the same time as DBCC
DBREINDEX has run already) for the rollback to complete.
--
Jacco Schalkwijk
SQL Server MVP
"Mitch" <anonymous@.discussions.microsoft.com> wrote in message
news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
> Can anyone explain what happens if you kill a dbcc
> reindex command (all indexes)?|||So if the process was blocking users to access a
database, and you killed the process, it just returns the
indexes back to their original form? Doesn't damage the
indexes?
>--Original Message--
>DBCC DBREINDEX is one transaction, and when you kill a
process, the
>transactions that it is running will be rolled back. So
you will end up with
>the same situation you had before you ran DBCC
DBREINDEX, and you will
>probably have to wait quite a long time (maybe the same
time as DBCC
>DBREINDEX has run already) for the rollback to complete.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Mitch" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
>> Can anyone explain what happens if you kill a dbcc
>> reindex command (all indexes)?
>
>.
>|||It will return the indexes to what they were before. Your users can't access
the table though while the rollback is in progress.
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:5e4a01c474c5$c87f45e0$a401280a@.phx.gbl...
> So if the process was blocking users to access a
> database, and you killed the process, it just returns the
> indexes back to their original form? Doesn't damage the
> indexes?
>>--Original Message--
>>DBCC DBREINDEX is one transaction, and when you kill a
> process, the
>>transactions that it is running will be rolled back. So
> you will end up with
>>the same situation you had before you ran DBCC
> DBREINDEX, and you will
>>probably have to wait quite a long time (maybe the same
> time as DBCC
>>DBREINDEX has run already) for the rollback to complete.
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Mitch" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
>> Can anyone explain what happens if you kill a dbcc
>> reindex command (all indexes)?
>>
>>.

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
TeoI has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
Teo
I has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo
|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!
|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.