Disconnect Users from Database

There are some operations that we execute in SQL Server which need exclusive access to the database. For everyone who has ever attempted this, I’m sure they’ve seen an error like this at least once.

There are a couple of ways to handle forcing exclusive access on a database. First we have the ALTER DATABASE method in T-SQL which is likely the most popular and most widely known.

[sql]ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE[/sql]

This method is highly effective. It sets the database to SINGLE_USER mode and the current connection will take ownership of that single user. In addition, the ROLLBACK IMMEDIATE option will KILL all other connections and force their active transactions to rollback.

The ALTER DATABASE method is not bullet-proof, however. For example, exclusive access is required on a database snapshot if you want to restore another database from the snapshot. You cannot set a database snapshot to SINGLE_USER mode which makes gaining exclusive access harder. So let’s look at another T-SQL method for killing all connections to a database, except our own.

[sql]declare @execSql varchar(1000), @databaseName varchar(100)
— Set the database name for which to kill the connections
set @databaseName = ‘MyDB’

set @execSql = ”
select @execSql = @execSql + ‘kill ‘ +
CONVERT(char(10), spid) + ‘ ‘
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
AND DBID <> 0
AND spid <> @@spid
exec(@execSql)[/sql]

This method is ten lines of code and is iterating through the current processes executing a KILL command via dynamic SQL. I don’t like this method, it feels sloppy to me, but most importantly it is difficult to remember and not quickly typed out. Likely, we all have snippet tools to solve that problem but let me show you another way.

[sql]Import-Module SQLPS -DisableNameChecking
$svr = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "localhost";
$svr.KillAllProcesses("Test1");[/sql]

This PowerShell method is one that I prefer. Its three lines of code which already speeds up the scripting and I find it easier to remember. Also, I tend to add the Import-Module command to my PowerShell profile, thus removing one more line of code.