Kill only user processes in SQL Server

Question

We have users that forget to close their access queries that use our SQL 2014 databases. When this happens it prevents the tables they are accessing from being rebuilt over night. Is there any way to Kill these users and not kill the system processes. From what I've read system processes are not only limited to SPID >50.

Answer 1

Killing user processes based on spid>=50 seems to be not reliable.

From Adam Machanic:Smashing a DMV Myth: session_id > 50 == User Process

A recent conversation on an MVP mailing list revealed that this magic number, while perhaps once a legitimate filter, is certainly not safe to use in SQL Server 2005 or SQL Server 2008. Several system features can--and will--use session IDs greater than 50, because there is simply not enough room otherwise.

Examples include:

• Large servers that use soft NUMA, because there is one checkpoint and lazy writer thread per NUMA node
• Asynchronous statistics updating, again (and especially) on larger servers
  Database mirroring, especially if a large number of databases are involved
• Service Broker activation, when a large number of activation tasks are being used

And there may be other cases as well. The point is, the number 50 is no longer a valid way to filter out system session IDs.

so your options are

SELECT *
FROM sys.dm_exec_sessions
WHERE
    is_user_process = 1

SELECT *
FROM sys.sysprocesses
WHERE
    hostprocess > ''

You can use above queries to get spids/session other than system and use kill command to kill them