I'm not refering to filtering sp_who2 as in following question
SQL Server: Filter output of sp_who2 but rather in regards of using the @loginame parameter to only get results back for specific user.
For example:
EXEC sp_who @loginame = 'sa'
EXEC sp_who2 @loginame = 'sa'
When running sp_who with @loginame parameters results are filtered only to specific user, but that behavior is not in sp_who2.
Based on the Connect items here
and here
I'm going to advise you that the use of undocumented features is something you do "at your own risk" because the results are not guaranteed and may, or may not, be available in future versions without much warning. Additionally, there may be bugs associated with the code that are left unaddressed. Finally, as you've mentioned, the only filter I've ever known to work with sp_who2 is to filter for 'active' connections. That's why I use DMVs to check for active connections.
If you can't deploy a stored procedure to your environment, you can also run the code below. It should be noted that it's very heavyweight and will produce "multiples" of sessions because it pulls the subtasks (execution contexts) associated with each session id. Additionally, it'll duplicate this further if you have an execution context that hits tempdb as well as a user db.
SELECT
instance_name = @@SERVERNAME,
GETDATE() AS collection_date,
s.session_id,
r.request_id,
DB_NAME(r.database_id) as request_database_name,
r.command,
w.exec_context_id,
w.blocking_session_id,
w.blocking_exec_context_id,
s.login_time,
s.host_name,
s.program_name,
s.client_interface_name,
s.login_name,
s.cpu_time AS session_cpu_time,
r.cpu_time AS request_cpu_time ,
s.memory_usage,
s.total_scheduled_time,
s.total_elapsed_time,
s.last_request_start_time,
s.last_request_end_time,
request_start_time = r.start_time,
s.reads as session_reads,
r.reads AS request_reads,
s.logical_reads AS session_logical_reads ,
r.logical_reads as request_logical_reads ,
s.writes as session_writes,
r.writes AS request_writes ,
r.wait_type AS request_wait_type ,
r.wait_time AS request_wait_time ,
w.wait_type AS waiting_tasks_wait_type ,
w.wait_duration_ms AS waiting_tasks_wait_duration,
SUBSTRING(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end -r.statement_start_offset)/2) as request_query_text,
CAST(qp.query_plan as XML) AS query_plan,
r.sql_handle AS request_sql_handle ,
r.plan_handle AS request_plan_handle,
w.resource_description,
t.transaction_id,
t.name,
t.transaction_begin_time,
t.transaction_type,
t.transaction_state,
t.database_transaction_log_record_count,
t.database_transaction_log_bytes_used,
t.database_transaction_log_bytes_reserved,
t.database_id,
t.database_transaction_state,
t.enlist_count,
t.is_user_transaction,
t.transaction_descriptor,
(SELECT
lock.resource_type AS resource_type,
lock.resource_subtype AS resource_subtype,
LTRIM(RTRIM(lock.resource_description)) AS resource_description,
lock.resource_database_id AS resource_database_id,
lock.resource_associated_entity_id AS resource_database_entity_id,
lock.resource_lock_partition AS resource_lock_partition,
lock.request_mode AS request_mode,
lock.request_type AS request_type,
lock.request_status AS request_status,
lock.request_exec_context_id as request_context_id,
DB_NAME(lock.resource_database_id) AS resource_database_name
FROM sys.dm_tran_locks lock
WHERE lock.request_session_id = r.session_id
AND lock.request_exec_context_id = w.exec_context_id
FOR XML AUTO, TYPE, ROOT('locks'))
AS locks,
tu.user_objects_alloc_page_count,
tu.user_objects_dealloc_page_count,
tu.internal_objects_alloc_page_count,
tu.internal_objects_dealloc_page_count
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
LEFT JOIN sys.dm_os_waiting_tasks w
ON s.session_id = w.session_id
LEFT JOIN (
SELECT
DISTINCT
at.transaction_id,
at.name,
at.transaction_begin_time,
at.transaction_type,
at.transaction_state,
dt.database_transaction_log_record_count,
dt.database_transaction_log_bytes_used,
dt.database_transaction_log_bytes_reserved,
dt.database_id,
dt.database_transaction_state,
st.enlist_count,
st.is_user_transaction,
st.transaction_descriptor
FROM sys.dm_tran_active_transactions at
JOIN sys.dm_tran_database_transactions dt
ON at.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_tran_session_transactions st
ON st.transaction_id = at.transaction_id
) t
ON t.transaction_id = r.transaction_id
LEFT JOIN sys.dm_db_task_space_usage tu
ON tu.exec_context_id = w.exec_context_id
AND tu.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as qt
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id > 50 AND r.session_id != @@SPID
-- AND s.login_name = 'sa'
After digging through sp_who2 code I found the bug. So explanation is quite simple.
There 4 parameters
DECLARE @sidlow VARBINARY(85)
,@sidhigh VARBINARY(85)
,@sid1 VARBINARY(85)
,@spidlow INT
,@spidhigh INT
They are defaulted to the following
SELECT @sidlow = CONVERT(VARBINARY(85), ( REPLICATE(CHAR(0), 85) ))
SELECT @sidhigh = CONVERT(VARBINARY(85), ( REPLICATE(CHAR(1), 85) ))
SELECT @spidlow = 0, @spidhigh = 32767
than if you passed in @loginame parameter @sidlow and @sidhigh get updated in the following statement.
IF ( @sid1 IS NOT NULL ) --Parm is a recognized login name.
BEGIN
SELECT @sidlow = SUSER_SID(@loginame)
,@sidhigh = SUSER_SID(@loginame)
GOTO LABEL_17PARM1EDITED
END
But once you get to the bottom of SP where it is suppose to returned @sidlow and @sidhigh instead the following code is executed.
SELECT @charMaxLenLoginName = CONVERT(VARCHAR, ISNULL(MAX(DATALENGTH(loginname)), 5))
,@charMaxLenDBName = CONVERT(VARCHAR, ISNULL(MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(128), DB_NAME(dbid))))), 6))
,@charMaxLenCPUTime = CONVERT(VARCHAR, ISNULL(MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(128), cpu)))), 7))
,@charMaxLenDiskIO = CONVERT(VARCHAR, ISNULL(MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(128), physical_io)))), 6))
,@charMaxLenCommand = CONVERT(VARCHAR, ISNULL(MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(128), cmd)))), 7))
,@charMaxLenHostName = CONVERT(VARCHAR, ISNULL(MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(128), hostname)))), 8))
,@charMaxLenProgramName = CONVERT(VARCHAR, ISNULL(MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(128), program_name)))),
11))
,@charMaxLenLastBatch = CONVERT(VARCHAR, ISNULL(MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(128), last_batch_char)))),
9))
FROM #tb1_sysprocesses
WHERE spid >= @spidlow
AND spid <= @spidhigh
As you can see instead @sidlow and @sidhigh it is using @spidlow and @spidhigh which were never changed. Therefore you still get all records back.
Now what does work is passing 'active'
EXEC sp_who2 @loginame = 'sa'
EXEC sp_who2 @loginame = 'active'
the first one will return all rows as discussed before, but the second execution will only return active rows because of the following code
--------Screen out any rows?
IF ( @loginame IN ( 'active' ) )
DELETE #tb1_sysprocesses
WHERE LOWER(status) = 'sleeping'
AND UPPER(cmd) IN ( 'AWAITING COMMAND', 'LAZY WRITER', 'CHECKPOINT SLEEP' )
AND blocked = 0
this code deletes all "not active" records from temp table #tb1_sysprocesses which will be used for return.
Conclusion:
This SP is bugged just like it was mentioned in links supplied by @swasheck, and based on reply from Microsoft they will not fix it. They recommend using DMVs instead of this.
Supplying valid login name to the procedure will not modify results.
EXEC sp_who2 @loginame = 'sa' --All Rows returned
Supplying 'Active' to the procedure will filter results only to "Active" records.
EXEC sp_who2 @loginame = 'active' --Only Active SIDs returned