Kill All User Connections in SQL Azure

How do I kill connections to Azure SQL database if I can't access it?

This issue was caused by Azure automated backups failing to end sessions correctly. None of the other answers worked as they all require you to be able to connect to the database, and I was unable to do this. I ended up speaking on the phone to Microsoft Support for some hours, during which they were also unable to connect to the database for the same reason.

The resolution, from their end, was to migrate the database to a new node, which is not something that Azure users can do, so if you encounter this level of issue, really the best (and only) thing to do is to contact Microsoft support ASAP.

I do recommend trying out some of the other suggestions here first, but without the ability to make any connections you will be stuck.

Because this occurred during the process of upgrading a database on Azure, we implemented a procedure of disabling automated backups before upgrading databases as a precaution, and the problem has not reoccurred for us.

kill Azure SQL DB sessions

I don't recommend killing sessions with out knowing what they are doing first..

so step1 would be to understand their current state..

select spid,blocked,waittime,waittype,lastwaittype,txt.text
 from sys.sysprocesses sp
 cross apply
 sys.dm_exec_sql_text(sp.sql_handle)  txt

The above dmv gives the state of all sessions and you can see if any of them are blocked ,if they are blocked, you can check the blocking session and try knowing why it is blocked by inputting blocking session spid like below

dbcc inputbuffer(blocking session spid)

With this you can try checking if the hung state you are experiencing is due to blocking.You can try resolving the blocking or try killing blocking spid

IF you don't see any blocking,but if think those sessions are hung,try running below dmv to see last read,write state..by this way you can check if the session is really doing some work

 select last_read,
last_write,session_id,connect_time
 from sys.dm_exec_connections

Try checking what the session is about before killing it(using input buffer..because, some may be internal processes like

SSISDB.internal.update_worker_agent_status;1

How do I disconnect open connections to an Azure SQL Managed Instance?

In RESTORE FROM URL in Managed Instance you can't even replace existing databases.

So either drop or rename the database before the RESTORE. Both DROP DATABASE MyDb and ALTER DATABASE MyDb MODIFY NAME = MyDb_old will kill existing connections to the database.

Alternatively you can use the Managed Point-in-Time Restore to restore an existing database to a previous point-in-time.

When restoring a backup, how do I disconnect all active connections?

SQL Server Management Studio 2005

When you right click on a database and click Tasks and then click Detach Database, it brings up a dialog with the active connections.

By clicking on the hyperlink under "Messages" you can kill the active connections.

You can then kill those connections without detaching the database.

More information here.

SQL Server Management Studio 2008

The interface has changed for SQL Server Management studio 2008, here are the steps (via: Tim Leung)

1. Right-click the server in Object Explorer and select 'Activity Monitor'.
2. When this opens, expand the Processes group.
3. Now use the drop-down to filter the results by database name.
4. Kill off the server connections by selecting the right-click 'Kill Process' option.

Cannot drop a database in Azure Data Studio, because it's currently in use

Someone else is connected, so you need to set it to SINGLE_USER. This will terminate any existing connections, so that the database can be dropped.

USE master;
GO

ALTER DATABASE zap SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE zap;
GO

How to close existing connections to a DB

This should disconnect everyone else, and leave you as the only user:

alter database YourDb set single_user with rollback immediate

Note: Don't forget

alter database YourDb set MULTI_USER

after you're done!

How do you close all connections to a local database in SQL Server Management Studio?

Take it offline first. THe dialog for that allows a force option. Then you can detach it safely.

---

Related Topics