Kill all connections to a database in SQL Server | SansSQL

Thursday, September 19, 2013

Kill all connections to a database in SQL Server

The easy and quick way to kill all connection to a SQL Server database is by setting the database to single user mode with ROLLBACK IMMEDIATE option.
This option will kill all the users immediately before setting the database to single user mode.
This will come handy while restoring a database and users are connected to it.

USE master
GO
ALTER DATABASE <DatabaseName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <DatabaseName>
SET MULTI_USER
GO

No comments:

Post a Comment

Ads