Setting Single-User Mode 

Use

Single-user mode can be set for the entire SQL Server or for individual databases using the Enterprise Manager. Once this mode has been set, only one connection to the SQL Server or specified database is allowed. Some administrative tasks can only be performed if single-user mode has been set.

Procedure

Single-User Mode for the SQL Server

  1. Before you set single-user mode for the server make sure no users are connected to the server.
  2. In the Enterprise Manager select the server.
  3. Choose Action ® Properties.

    4. The SQL Server Properties dialog box opens.

  4. On the General tab choose Startup parameters.

    5. The Startup Parameters box appears.

  5. Enter the parameter -m and choose Add.

    6. The new startup parameter is displayed in the Existing Parameters box.

  6. Press OK to confirm the entry and restart the server.

Only one connection to the server is now possible.

When single user mode has been set, only one connection to the server is permitted. Therefore, keep the following in mind:

If the SQL Server Agent is running it occupies the only connection available to the server and you will be unable to start the Enterprise Manager or Query Analyzer. Stop the agent to enable a connection to the Enterprise Manager.

If you are working with the Enterprise Manager and want to start the Query Analyzer, you first have to close the Enterprise Manager. Then start the Query Analyzer via the NT Start menu.

Single-User Mode for a Database

  1. Before you set single-user mode for the database, make sure no connection to the database is open. For example, if the Query Analyzer is active, close it.
  2. In the Enterprise Manager, expand the server. Expand Databases .
  3. Select the <SAPSID> database and choose Action ® Properties.

    4. The Properties dialog box appears.

  4. On the Options tab select Single user and confirm your entry with OK.

Once single user-mode has been set, only one connection to the database is permitted. Each utility, including the Enterprise Manager, Query Analyzer and isql opens a separate connection to the server.