KILL

Question

KILL

Tara-1044200

SSCoach

Points: 15951
More actions
June 10, 2009 at 11:03 am

#134985

I am just confused with this 2 processess.
Does sql server roll back the transaction when i cancel an insert query from the query window, does it act as the same using KILL command on a session?
2. Can a developer without sysadmin previalges use kill command for his own session?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Tara (6/10/2009)
I am just confused with this 2 processess.
Does sql server roll back the transaction when i cancel an insert query from the query window, does it act as the same using KILL command on a session?
2. Can a developer without sysadmin previalges use kill command for his own session?

kill rollsback any transactions for the spid in question, as well as terminating the spid's connection to the server.

cancelling a query or statement while it is running just cancels and rollsback, it does nto terminate your connection.

if you select @@spid to find your s, and then try to kill it,you will simply raise an error that says you can't kill your own connection("Cannot use KILL to kill your own process"), and KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable., so a non admin cannot kill other processes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

But when a developer canceled the the query it was still showing as INSERT in the activity monitor and so i killed the session and it appeared as KILLED/ROLLBACK in the activity monitor.

Lowell SSC Guru Points: 323518 More actions · Answer 3

that would be normal....if i started an insert or update that was tweaking a million rows, and then tried to cancell it, it certainly might take a while to rollback any work that was being done...but eventually it would rollback.

I'm not sure but when you killed his process broke his connection, the rollback still occurred. when you issue kill, i don't think YOU have to wait for the rollback to complete...i think it might be asyncronous to your process, where his original spid would have to wait for it to finish before he could issue another command.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

I think using

KILL 54 WITH STATUSONLY;

will give the status but if i already issue KILL without status, how can i generate kill status?

when i executed the same command again it gave me 0%