Using the Kill command in a VB app

Question

Using the Kill command in a VB app

webtekkie

SSCommitted

Points: 1901
More actions
August 23, 2010 at 8:41 am

#237079

Hiya,
Our company has a VB6 application which, amongst other things, runs some fairly heavy queries against a SQL database for reporting purposes. They have expressed a desire to use the Kill command to cancel any queries that are running where the user has chaned their minds. I'm more than a little nervous about this, not least because of potential rollbacks. Although they are only select queries, there's the possibility of them using tempdb and so the cancellations could have to rollback transactions there. And in any case, I would have expected the connection to SQL to be dropped when the "con.close" and "set con = nothing" are called (the app uses ADODB).
Has anybody come across a request like this before?
What are your thoughts?
Thanks
Martin

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

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

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 1

Yes I have and my answer at the time was no...

You could look at offering it if they are using a read only connection or something, but yeah the rollback could be a killer.

steveb. SSC-Forever Points: 46733 More actions · Answer 2

It's not something I would be happy with.

Have you had a look at the queries and tried the standard tuning steps on them?

they could possible be missing a couple of important indexes or a query re-wite. I would look at optimising the queries first before trying anything more radical.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

KILL should only be done manually by a trained DBA on a production environment. Period. A user app should NOT be able to kill processes. Too unpredictable as to the results.

webtekkie SSCommitted Points: 1901 More actions · Answer 4

Thanks for your feedback, guys - that confirms my opinion. Is anybody able to give me definitive reasons that I can go back to the dev team with to say "This is why you shouldn't do it...." (I'm aware of the rollbacks in tempdb, of course).

Thanks again

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 5

A kill can have block implications outside of the realm they are aware of.

Depending on the nature of how connections are made there is a risk you may kill something that you do not intend to kill. (killing the wrong spid because you stuff is in fact done and something else is now using that spid).

Since the KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable.

It would require those user connections to have rights sigificantly beyond the scope needed for other activities on those accounts, connections.