> ## Content Index
> Fetch the complete content index at: https://blog.tsd.digital/llms.txt
> Use this file to discover other available public pages before exploring further.

# Drop/Kill all connections to a SQL Database
- URL: https://blog.tsd.digital/dropkill-all-connections-to-a-sql-database/
- Published: 2019-04-04T14:48:04.000Z
- Updated: 2019-04-04T14:48:04.000Z
- Author: Tim Gaunt
- Tags: SQL, Useful Script, SQL Server, #Import 2025-04-01 05:38

Every now and again you need to get exclusivity to a database e.g. to do a database restore but someone is connected. You can manually find who it is using EXEC sp\_who but that can result in having to manually kill the various connections.

This is a little script we've found handy in the past to drop all connections to a SQL Database. I hope it's of use to you.

```
USE master

DECLARE @dbid INT 
SELECT 
	@dbid = dbid 
FROM 
	sys.sysdatabases 
WHERE 
	name = '## Your Database Name Here ##'

IF EXISTS (SELECT spid FROM sys.sysprocesses WHERE dbid = @dbid)
  BEGIN
	PRINT '-------------------------------------------'
	PRINT 'CREATE WOULD FAIL -DROPPING ALL CONNECTIONS'
	PRINT '-------------------------------------------'
	PRINT 'These processes are blocking the restore from occurring'

	SELECT spid, hostname, loginame, status, last_batch
	FROM sys.sysprocesses WHERE dbid = @dbid

	--Kill any connections
    DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
	SELECT spid FROM master.dbo.sysprocesses WHERE dbid = @dbid
    DECLARE @SysProcId smallint
    OPEN SysProc
    FETCH NEXT FROM SysProc INTO @SysProcId
    DECLARE @KillStatement char(30)
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))
        EXEC (@KillStatement)
        FETCH NEXT FROM SysProc INTO @SysProcId
    END

    WAITFOR DELAY '000:00:01'
  END
```