Autometic Kill

Question

Post reply

Autometic Kill

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
November 2, 2009 at 10:06 pm

#213368

Dear All,
We have a software product for POS, The Products is used by over 300 machine in all over the country,
Here we need to kill process id, Which is idle above 10 min with out "sa" user id , Can any one send me the solutions,
' My Attempt is ,
Procedures for killing
sheduling the jobs for every one min...
But my procedures is not working..'

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

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

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 1

1. Which Version of SQL Server you are using?

2. Are you sure you do not have any slow running query in your system?

3. If 2 is correct then what is value for program name when you run sp_who2?

[font="Verdana"]--www.sqlvillage.com[/size][/font]

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 2

anyhow Thanks for post,

I done that with the help of sys.DM_EXEC_SESSIONS tables

and job shedule...

einman33 SSCrazy Points: 2813 More actions · Answer 3

Automatic kill can be a death sentence especially when the rollback kicks in. I would personally go a different route to figure out why this spid is staying active longer than desired than just running around the data tier with a sawed off shotgun wacking spids.

EDIT:

Noticed you say inactive, this may not necessary be a bad thing as it could just be a connection sitting in the pool ready for reuse.

Lowell SSC Guru Points: 323518 More actions · Answer 4

einman33 (11/3/2009)
Automatic kill can be a death sentence especially when the rollback kicks in. I would personally go a different route to figure out why this spid is staying active longer than desired than just running around the data tier with a sawed off shotgun wacking spids.
EDIT:
Noticed you say inactive, this may not necessary be a bad thing as it could just be a connection sitting in the pool ready for reuse.

do not kill connections unless you have a very specific reason for doing so...it sounds like you looked at the DMV and decided "aw they don't need to be connected, they are not doing anything"

I'm on the same page as einman; the spids could simply be connection pooling, and idle connections are not an issue, anyway.

unless you KNOW those idle spids are causing blocking trouble(which would imply an application that needs to be fixed) , you should never try to kill them. you may end up creating problems for users because they would have no idea you are deleting their connections, and all of the sudden they need to restart their app all the time...would the IT department KNOW you are killing spids for unknown reasons?

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!

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

As discussed in the similar thread a few days ago, there may be a better solution. Discussing WHY you want to kill the spid, may lead to new ideas.

boopathy_sri Newbie Points: 3 More actions · Answer 6

Hi.. This can be done by creating T-SQL by running using batch file..