Home > Alter, SQL Queries, SQL Server, T-SQL > Changing SQL database to single user mode and back to Multi User mode

Changing SQL database to single user mode and back to Multi User mode

It is often necessary to change the database to single user mode, especially if you are a DBA. A simple example would be to change the collation settings or any DB settings. The single user will allow only one  user ( usually DBA) to access the database. Hence it will be easy to make changes without the worry of deadlocks or any other type of contention for DB and also without affecting the users.

It is very easy to change the database to Single user mode; in fact, it is just an execution of the script away. Use the script below to change the mode.

ALTER  DATABASE <<Database Name>>  SET SINGLE_USER  WITH NO_WAIT

The NO_WAIT clause will set it to single user mode as soon as you execute the query.  An alternate to this is by using the system stored procedure sp_dboption

Continue Reading ….

 

 

 

Advertisements
  1. December 1, 2010 at 4:53 am

    Just a tip since you’re using wordpress encase your sql code like so:

    ALTER DATABASE MyDBName SET SINGLE_USER WITH NO_WAIT
    

    and it will format nicely.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: