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'
To alter the database state to restrict the access to the single user mode,
first open the Microsoft SQL Server Management Studio, and connect to a SQL
Server instance. Open the list of available databases belonging to the related
instance in the Object Explorer window. Right click on the sql server database
that you want to set its mode to single user and select Properties in the
context menu.
When you click properties menu item, the following Database Properties screen
will be displayed for the selected database. I used database named Works for
samples used for the article.
Select the Options page from the list in the left side of the screen. If you
scroll down the options list for State options, you will see Restrict Access
database options with three options listed in the combo box.
Restrict Access modes : Multiple, Single and Restricted modes.
If you select Single mode and click the OK button, you can either alter the
database access mode to single user successfully or you will fail to change the
access mode because of existence of active open connections to the Works
database. The Management Studio IDE will prompt you to close all other
connections to the related database for a successfull alter database option
process.
To change the database properties, SQL Server must close all other
connections to the database. Are you sure you want to change the properties and
close all other connections?
After the alter command runs successfully, the database Works will be
displayed as shown in the Object Explorer window.
If an active connection exists other than the Management Studio, and you
click the database Works within the SQL Server Management Studio, the following
warning message will be displayed:
The database Works is not accessible. (ObjectExplorer)
If you right click on the database Works, the following error message will be
displayed.
Database 'Works' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)
After a database is altered as single user mode, it can be altered again back
to multi user mode by running the below sql command.
ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT
But if this command is run from a connection that is not the only active
connection to the related database, the command will fail with the following
message:
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'Works' cannot be made at this time.
The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
In a case that you want to alter the database access mode from single user
mode to multi user mode or from multi user mode to single user mode, you may
have to kill all the active open connections to the database.
In such a case, you have to find all the open connections and run "Kill @spid"
command to close all the connections.
For a list of open connections for a specific database you can run a similar
command as below :
select spid from master..sysprocesses where dbid = db_id('Works') and spid <>
@@spid
The second part of the task will be closing the open connections with the
Kill command. This can be achived by running kill commands within a cursor or
creating a dynamic sql command which kills the active connections to the related
database.
Let's code the closing open connections using a t-sql cursor:
DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int
SET @DatabaseName = N'Works'
DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @SPId
WHILE @@FETCH_STATUS = 0
BEGIN
KILL @SPId
FETCH NEXT FROM my_cursor INTO @SPId
END
CLOSE my_cursor
DEALLOCATE my_cursor
After all connections are closed except the currently running prcess (@@spid)
if we are trying to alter the access mode of the database that we're in from
multi user mode to single user mode, we can now run the ALTER DATABASE command
for single user mode or multi user mode.
ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT
or
ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT
You can also use the following sp_dboption sql commands to alter the
database option for single user. If you set the "single user" database
option to "true", this is same as altering the database as "SINGLE_USER"
with ALTER DATABASE statement.
USE master
GO
EXEC sp_dboption 'Works', 'single user', 'false';
If you run the below sp_dboption command which sets the "single user"
to "false", this is same as altering the database as "MULTI_USER"
with ALTER DATABASE statement.
USE master
GO
EXEC sp_dboption 'Works', 'single user', 'true';
The main difference between sp_dboption and ALTER DATABASE
commands is that, sp_dboption will not be supported with further releases of SQL
SERVER after SQL SERVER 2005.
 BlinkList
 Del.icio.us
 Digg
 Furl
 Simpy
 Spurl
 DZone
 ma.gnolia
 Shadows
|