|
|
|
SSChasing Mays
 
Group: General Forum Members
Last Login: 2 days ago @ 9:55 AM
Points: 624,
Visits: 1,267
|
|
Hi All
I am not sure wether this is a problem or not, when i use SP_WHO2 on one of my servers i can see process that are 2 days old on some instances. is this a normal behavior on SQL server,
If this is not the case, then how can i identify this process that are 2 days old and is it advisable to kill these process,
there are also some process that takes a long time to process, espically on a linked server environment, is there any ways that we can stop this happening.
Please advice me on this.
Cheers
:)
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 2,731,
Visits: 22,030
|
|
Some system processes remain active from the starting of the MSSQL service to the stopping on the service.
If the processes in question are not system processes (coming from an application) it may be by design, but typically indicates a poorly written application in which active connections are not cleaned up. Having connections remain open forever can use unnecessary resources or even retain objects locks on your server. The processes should be identified and if they are supposed to be closed, the offending application should be fixed.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, June 04, 2009 8:16 AM
Points: 29,
Visits: 158
|
|
I've used this to kill certain client-driven processes that last longer than a certain timeout period. I'm able to grab some more detailed information via the following query
SELECT r.session_id , r.blocking_session_id , r.database_id,s.program_name
, s.host_name, t.text, wait_type, wait_time
INTO #tmp
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1
and text not like ('%sp_who3%')
SELECT * from #tmp
From there, if a r.blocking_session_id has a wait_time of over a certain period of time, and is blocking an r.session_id, it automatically kills the process and emails me the results. It works like a charm to stop any processes that excessively block for us. You could also use it to kill anything that simply has a wait_time of over a certain period, although there are going to be many, many system processes you do not want to kill.
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: 2 days ago @ 9:55 AM
Points: 624,
Visits: 1,267
|
|
Thanks mate, but can i make this as a job that runs every 1 hour and kills the process that runs for a long time
Cheers
:)
|
|
|
|
