Putting Database in to a single user mode

Objective: Bring the database to single user mode to do some maintenance.


You can use this sp_dboption in previous version like SQL 2000 and earlier.


sp_dboption 'dbname', 'single user', true

You can use this Alter syntax for versions like SQL 2005 and later

ALTER DATABASE DBNAME SET SINGLE_USER

ALTER DATABASE DBNAME SET SINGLE_USER with no_wait

ALTER DATABASE DBNAME SET SINGLE_USER with rollback after 5

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATELY

GUI mode:

Also you can do from Database GUI mode. Highlight database and righ click to get the Properties - Options - State - There you will find option.

-----------

Drop database connections:

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'test'
--SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

SELECT @SQL
EXEC(@SQL)
-------------------OR--------------------------------
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'test'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

SELECT @SQL
EXEC(@SQL)

------------------------------





How to Alter a SQL Server Database as Single User Mode and as Multi User Mode


You can use the SQL Server Enterprise Manager for SQL Server 2000 to set a database to a single user mode or to multi user mode. Similarly, SQL Server Management Studio can be used for SQL Server 2005 for changing a database to single user mode or to multi user mode. Also you can alter the database access mode by using sql commands like ALTER DATABASE and sp_dboption.



ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

or

EXEC sp_dboption 'Works', 'single user', 'false'

EXEC sp_dboption 'Works', 'single user', 'true'