Query To Kill All Active Connections in Azure SQL Database

Suppose you want to kill all sessions for a specific Azure SQL Database. In the on-premise SQL Server, we use the KILL SPID statement for terminating a session id. Is it applicable to Azure SQL Database also? Let’s find out.

The KILL SPID statement is available for Azure SQ Database and Azure SQL Managed Instance. The following script kills all connections to the Azure database specified in the where clause.

DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'

FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
   ON c.session_id = s.session_id
JOIN sys.dm_exec_requests AS r
ON s.session_id=r.session_id
WHERE c.session_id <> @@SPID
--and status = 'sleeping'
AND r.database_id=db_id('MyTestDB') --Enter Azure SQL Database Name
ORDER BY c.connect_time ASC
PRINT @kill
EXEC(@kill)

For demonstration, let’s connect to the Azure database and start a session with the following script. In my demo, it uses SPID 87.

CREATE TABLE tempdb
(
id int
)

WHILE (1=1)
INSERT INTO tempdb values(1)

Now, open another session and execute the query to kill SPID. It kills the SPID and returns the killed SPID, as shown below.

You can also explore the article on SQLShack to understand KILL SPID command in SQL Server.