I'm configuring a job to get the SPID of an offending process and kill it. I'm using a mix between the great sp_Whoiscative from @AdamMachanic and joining with sysprocesses on login name and SPID and session_id values as those are supposed to be the same (or I'm wrong and then that's my error?).
I'm getting something I don't understand: for a same SPID from sysprocesses I'm getting different session_id values from whoisactive results. What is incorrect here?
Here is the code I'm using. The filter to get values 10s before current datetime is because the whoisactive is running with the parameter @deltainterval that takes about 10s to execute on each run.
USE master;
-- Log information about current running processes to table Log_WhoIsActive
EXEC [master].[dbo].[sp_WhoIsActive]
@get_full_inner_text = 1,
@get_plans = 2,
@get_outer_command = 1,
@get_transaction_info = 1,
@get_task_info = 2,
@get_locks = 1,
@get_avg_time = 1,
@get_additional_info = 1,
@delta_interval = 2,
@sort_order = '[start_time] DESC',
@destination_table = 'Log_WhoIsActive_tests';
--compare date from sysprocesses with data gathered on sp_whosiactive
SELECT dt.collection_time, sp.spid, dt.[dd hh:mm:ss.mss], sp.loginame,
dt.login_name, dt.session_id
FROM sys.sysprocesses sp JOIN Log_WhoIsActive_tests dt
ON sp.loginame = dt.login_name
WHERE sp.loginame = 'my_login_name'
AND sp.status = 'runnable'
AND sp.spid > 50
AND dt.collection_time > DATEADD(ss, -10, GETDATE());
Best Answer
SPID === session_id.
What you are seeing is a Cartesian product since you are joining on a field that is not unique within the data set. You currently have:
You should probably, at the very least, add the following condition to that JOIN:
Also, please do not use
sysprocesses
. It was deprecated when SQL Server 2005 was released, and thesys.sysprocesses
"table" you are using is just a compatibility view. The MSDN page for sys.sysprocesses even has a note at the top stating:You should instead use the new DMV (Dynamic Management Views):
Please note that a Login might need to be granted the server-level permission of
VIEW SERVER STATE
in order to see more than their own data forsys.dm_exec_sessions
andsys.dm_exec_requests
, or see anything at all insys.dm_exec_connections
.If this permission is needed, then the current database when you run the
GRANT
statement needs to bemaster
as this is a server-level permission.