0

I have been looking all over StackExchange, testing various methods listed here, without any success.

I need to SELECT only users that have multiple sessions. This is my beginning SELECT statement:

SELECT  spid SPID,
        loginame [Login]
FROM master.dbo.sysprocesses sp 
WHERE (hostname LIKE '%P21%' OR hostname LIKE '%TS%')
    AND PROGRAM_NAME LIKE '%SQLCA%'
ORDER BY Login 

For example, the following does not work:

SELECT  spid SPID,
        count(loginame) as Login
FROM master.dbo.sysprocesses sp 
WHERE (hostname LIKE '%P21%' OR hostname LIKE '%TS%')
    AND PROGRAM_NAME LIKE '%SQLCA%' 
HAVING count(loginame) > 1;

This results in an error:

Column 'master.dbo.sysprocesses.spid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What can I do to narrow the results down so that it only outputs when the loginname is > 1 ?

EDIT: It may help if I explain the ultimate goal -- Our ERP system allows users to log in multiple times. However, each login consumes a seat on our license. I'm looking to create a SQL job that periodically runs and automatically KILLS the oldest spids for users that have multiple spids.

The first step for that is simply getting a list of spids from users that have multiple spids, which meet the criteria in my WHERE. After that, I'll have to fine-tune it down to the oldest spid per user, before finally running a KILL on those spids. I'm not asking anyone to create the entire script. Any assist with just getting the list of spids for logins with multiple sessions would be terrific.

0

1 Answer 1

1

If you want a single row per login, then SPID makes no sense in the output unless you have a clear definition of what you want there (the lowest value? highest? one associated with the earliest or latest login? a comma-separated list of all of them?). Also sysprocesses has been deprecated for 16 years and more than 6 major releases ago (more info here); use the newer sys.dm_exec_* DMVs.

SELECT login_name, session_count = COUNT(*)
  FROM sys.dm_exec_sessions
  WHERE program_name LIKE N'%SQLCA%'
    AND (host_name LIKE N'%P21%' OR host_name LIKE N'%TS%')
  GROUP BY login_name 
  HAVING COUNT(*) > 1;

If you want all the individual rows and include each session_id, you can use a CTE with a window function to determine the count of sessions for each login, then filter outside the CTE:

;WITH cte AS 
(
  SELECT session_id, login_name, 
    session_count = COUNT(*) OVER (PARTITION BY login_name)
  FROM sys.dm_exec_sessions
  WHERE program_name LIKE N'%SQLCA%'
    AND (host_name LIKE N'%P21%' OR host_name LIKE N'%TS%')
)
SELECT SPID = session_id,
  login_name, 
  session_count
FROM cte 
WHERE session_count > 1
ORDER BY login_name, session_id;

For killing sessions to comply with seat counts, I think it makes much more sense to kill all but the newest session, which you can determine in a similar way with a different window function:

;WITH cte AS 
(
  SELECT session_id, login_name, 
    session_count = COUNT(*) OVER (PARTITION BY login_name),
    rn = ROW_NUMBER() OVER (PARTITION BY login_name ORDER BY login_time DESC)
  FROM sys.dm_exec_sessions
) 
SELECT [Command] = CASE 
  WHEN rn > 1 THEN 
    'KILL ' + CONVERT(varchar(5), session_id) + N';'
  ELSE 
    '-- Leave this one alone' 
  END, *
FROM cte
WHERE session_count > 1 
ORDER BY login_name, rn DESC;

If you want to kill all but the oldest for some reason, change:

rn = ROW_NUMBER() OVER (PARTITION BY login_name ORDER BY login_time DESC)

To:

rn = ROW_NUMBER() OVER (PARTITION BY login_name ORDER BY login_time)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.