DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to put a database into single-user mode?

The following is the Microsoft SQL Server T-SQL command for it:

ALTER DATABASE pubs SET SINGLE_USER

Stop SQL Server Agent prior, otherwise may take the only connection and lock you (sysadmin) out.

If there are still users, they can use the database for the current session. Of course, you can kill the users' spid-s to force them out. To rollback incomplete transaction, use:

ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTRICTED_USER provides for only members of the db_owner fixed database role, dbcreator and sysadmin fixed server roles to connect to the database in one or more connections:

ALTER DATABASE pubs SET RESTRICTED_USER

To return to normal multi-user state from single-user mode:

ALTER DATABASE pubs SET MULTI_USER

If you get Error Msg 5064, that means there is a live connection to the database. Make sure you are not connected to it in a Query Editor session. If it shows in the Available Databases window, reposition to tempdb or any other db. Otherwise, you can locate the connecting session by exec sp_who and use kill <SPID> to terminate the connection.

 

Related article:

How to: Set a Database to Single-user Mode (SQL Server Management Studio)

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE