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());