top of page

How to close connections to MS SQL Database

When you try to take a database offline in Microsoft SQL Server and it gets stuck, this usually means there are active connections or transactions that are preventing the operation from completing. Here's how you can address this issue:



Identify Active Connections: First, you need to find out if there are any active connections to your database. You can use the following SQL query to do so:


USE master;
GO
SELECT spid, status, loginame, hostname, blocked, db_name(dbid) as dbname, cmd
FROM sys.sysprocesses
WHERE dbid = DB_ID('YourDatabaseName');

Replace 'YourDatabaseName' with the name of your database. This query will list all processes connected to your database.


Kill Active Connections: If there are active connections, you can terminate them. Use the KILL command followed by the session ID (spid) for each connection:


KILL [session_id];

Replace [session_id] with the spid of the process you want to terminate. Be cautious with this command, as it will immediately terminate the selected session and can cause loss of unsaved work.


Enjoy 😎


AlexIn Tech


55 views
bottom of page