Disconnect Users from Database – SQL Hammer

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.

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
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.

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)

This method is ten lines of code and is iterating through the current processes executing a KILL.aspx) 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.

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

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.aspx), thus removing one more line of code.

Written by Derik Hammer of SQL Hammer

Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability, disaster recovery, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and entrepreneurial ventures.

Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com as one small way to give back and continue the cycle of shared learning.

Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource.

For more information, visit http://www.sqlhammer.com. Follow Derik on Twitter for SQL tips and chat