How to identify each connection inside SQL Server.

Problem: How to identify each connection inside SQL Server.

Solution: @@SPID

Server Process ID(SPID) is independent to each connection by @@SPID.

Each connection has its own SPID, for example a user executes a query the SERVER assigns a SPID which is available and once the query is terminated it assigns it to next user who runs a query.

Example:

In this article I am going to show you how we can retrieve SPIDs, information related to SPID.

Example 1:

To retrieve the Session connection information

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';

The SPID is also shown at the query results window. 

Example 2:

--the system view stores all the execution sessions information, SPID’s.

SELECT * FROM sys.dm_exec_sessions

Example 3:

--Kill all the active connections on SERVER. Dont run this query  on the live enviroment.

DECLARE @ExecSQL VARCHAR(1000),

                @DBName VARCHAR(100) = 'DB'

SELECT @ExecSQL = @ExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '

FROM master.dbo.sysprocesses

WHERE DB_NAME(dbid) = @DBName

                AND DBID <> 0

                AND spid <> @@spid

                -- EXEC @ExecSQL