kill idle process automatically

Dear all,
is there a way of killing those processes automatically by the server
where the processes have been idle for long time (where the last SQL batch
was executed >45 mins ago)?
Thanx in advanced.
Lee
Learn
I afraid there isn't, but using sp_who2 system stored procedure you will be
able to see all activities on the server as well as status of the
processes.
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OdnYO08ZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>
|||Hi,
Killing the sleeping user process may not be a good idea. A user processes
may be running for 40 minutes
and when you check that process may be sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
How to do:-
To do this you can Query the MASTER..SYSPROCESSES table for Login_time ,
Status,cpu columns
compare the values with your threshold (say 45 minutes) and then use the
command KILL <spid> to kill the process.
Thanks
Hari
MCDBA
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OdnYO08ZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>
|||Learn Yee,
Why do you want to do that? Could you describe your problem in further
detail? I would not recommend this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Learn Yee wrote:
> Dear all,
> is there a way of killing those processes automatically by the server
> where the processes have been idle for long time (where the last SQL batch
> was executed >45 mins ago)?
> Thanx in advanced.
> Lee
>