How to Close Connections to an MS SQL Database

When you attempt to take a database offline in Microsoft SQL Server and it fails, it usually means that active connections or transactions are preventing the operation from completing. Here’s how to close connections to an MS SQL database and resolve this issue.

Identify Active Connections: First, you need to check if there are 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 your actual database name. This query will list all processes connected to your database.

Terminate Active Connections: If active connections exist, 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 end the selected session and may result in the loss of unsaved work.

Enjoy 😎

AlexIn Tech