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..
from sys.sysprocesses sp
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
Try checking what the session is about before killing it(using input buffer..because, some may be internal processes like
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)
- Right-click the server in Object Explorer and select 'Activity Monitor'.
- When this opens, expand the Processes group.
- Now use the drop-down to filter the results by database name.
- 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.
ALTER DATABASE zap SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE zap;
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.