Kill the windows user

Question

Post reply

Kill the windows user

vivekinmadar

Right there with Babe

Points: 745
More actions
October 21, 2011 at 1:45 am

#244930

hi
if any query which is runnning with the windows user exmple ayli600 and should be killed, if it is running for more than 30 minutes we have create t sql script to complete this task
so please reply it is very urgent
vivek

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

Have a look at the Kill function,

though be aware that this can sometimes take a long time to complete if there is a lot of work to undo.

vivekinmadar Right there with Babe Points: 745 More actions · Answer 2

Hi thanks reply

dude .. i want how find the running windows user after if windows user is running more the 30 mint then system has kill the user this continously ..

i want do make one job ... please provide script if possible

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

you can then use

sys.sysprocesses

to get the SPID of the connection and then pass this to the KILL function.

vivekinmadar Right there with Babe Points: 745 More actions · Answer 4

Thanks for reply

i have checked over there but i want go with only particular database for exmple

if any query which is runnning with the windows user ayli600 and should be killed, if it is running for more than 30 minutes. And the trigger, should not kill if a query which is running with the user SNI_P02_ODSSIB and SNI_P02_iProcess

please reply with query

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

you can filter on loginname to exclude those ones you dont want.

Though i have to say writing this kind of function into your server could cause issues, and I feel that there must be a better way of dealing with the issues.

Lowell SSC Guru Points: 323518 More actions · Answer 6

steveb. (10/21/2011)
you can filter on loginname to exclude those ones you dont want.
Though i have to say writing this kind of function into your server could cause issues, and I feel that there must be a better way of dealing with the issues.

i feel the same; i was going to post a query to find and kill the user, but although it's pretty easy to find and kill the session, it's not the right thing to do;

if he has a long running processes, he needs to fix and speed up, tune the queries, eliminate the blocking, whatever it is that makes the query take 30 minites instead of seconds.

I'm with steveb;

let us help ou find the solution, instead of this idea.

You clearly know what process takes too long...what does it do? is it one query or a stack of them?

does it use a CURSOR?

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!

vivekinmadar Right there with Babe Points: 745 More actions · Answer 7

thnaks for reply

your telling write but my cleint create any trigger on theses database using windows user if that particular trigger is take more then 30mintes or 1800000 Milliseconds then the function has kill the session ..

I dont know about much about cursor.. could help if understood my above sentance

please

vivek

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

I guess the problem is that certain users are running large inefficent queries that take a lot of cpu and time to complete, is this correct?

The issue you will have when you start killing these process, is that they have to roll-back everything they did so if they are running for 30mins it could take 30mins to roll-back possibly longer. So rather than kill processes it would be better to look at why these queries are taking so long and fix them at the source.

Also if a user gets their process killed without any notifcation from anyone they are more than likely to just start the query again,

vivekinmadar Right there with Babe Points: 745 More actions · Answer 9

I guess the problem is that certain users are running large inefficent queries that take a lot of cpu and time to complete, is this correct?

This correct ....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

vivekinmadar (10/21/2011)
I guess the problem is that certain users are running large inefficent queries that take a lot of cpu and time to complete, is this correct?
This correct ....

Better to enhance the performance of the queries. Users who have their session unexpectedly terminated more than one or two times will look for a dba to defenestrate.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden