single user

 

Setting TEMPDB to single user

no other process will be able to access the tempdb other than ur current process

* at the time that you set the database to single-user mode,
  their connections to the database will be closed without warning

* The database remains in single-user mode even if the user that set the option logs off.
  (At that point, a different user, but only one, can connect to the database.)

* Before you set the database to SINGLE_USER,
  verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF
 
=====================
Set Database into Single User Mode

sp_dboption 'tempdb', 'single user', 'true'

sp_dboption 'tempdb', 'single user', 'false'

=====================
Start SQL Server in Single-User Mode

startup option "-m"

When you start an instance of SQL Server in single-user mode, SQL Server Management Studio can connect to SQL Server. Object Explorer in Management Studio might fail because it requires more than one connection for some operations.

net start MSSQLSERVER /m

Clustered Environment:

For SQL Server installation in a clustered environment, when SQL Server is started in single user mode, the cluster resource dll uses up the available connection thereby blocking any other connections to the server. When SQL Server is in this state, if you try to bring SQL Server Agent resource online, it may fail over the SQL resource to a different node if the resource is configured to affect the group.
 

 

Creative Commons license icon Creative Commons license icon