DBA and SysAdmin World

February 28, 2009

Starting SQL Server in Single Mode User

Filed under: SQL Server — Tags: , — solihinho @ 8:07 AM
Single user Background
Last time I posted how to set database in single mode. This time I faced a problem with DBCC CHECKDB. Sometimes it takes almost 1 hour to examine 1 database but sometimes only take 10 minutes. I suspected the problem is in the disk. There are many transactions make the disk IO high. I needed to try run DBCC CHECKDB while there are not another transactions allowed to process. Rather than I have to shut down another application servers, I prefer to make the SQL Server in single mode so only one user allowed to connect to the SQL Server.

Step by Step
1. Stop SQL Server service
2. Enter console mode by type cmd from start -> run
3. Go to folder where sqlservr.exe located e.g.: C:\Program Files\Microsoft SQL Server\MSSQL\Binn
4. Type sqlservr.exe -m for SQL Server is not use instance name) or sqlservr.exe -m -s[Instance Name] for SQL Server that use instance name.
5. Connect to the SQL Server use user with system administrator role
6. At the end, type Ctrl+C in console screen, type “Y” on “Do you wish to shutdown SQL Server (Y/N)?”
7. Start SQL Server service.

Time out If your application use SQL Server user with role as system administrator like “sa”, you may face problem when try to connect to SQL Server in Single Mode User because there is possibility the application already connect to the SQL Server before you do.
Advertisements

December 14, 2008

How to Set Database into Single User Mode

Filed under: SQL Server — Tags: , — solihinho @ 1:57 AM
Single user Today when I ran DBCC CHECKDB on my development server, I found errors on one database. When I tried to repair it, there is a message “Repair statement not processed. Database needs to be in single user mode.

Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

To change a database into single user mode, we can use sp_dboption. But since this feature will be removed in future version after SQL Server 2000, we use ALTER DATABASE instead of.

Using T-SQL

Set to single mode:
   ALTER DATABASE [database name] SET SINGLE_USER;

Set back into multi user mode:
   ALTER DATABASE [database name] SET MULTI_USER;
Coffee MASTER, MSDB and TEMPDB cannot be set into single user mode. For this you need to start the SQL SERVER into single user mode.
Idea If the command ALTER DATABASE is failed, may be there are another sessions use this database. Use SP_WHO2 without active parameter to find out. Kill related spid that connecting to the database. If using SQL Server 2000, simple way is using EM, right click on the database and choose detach database. On detach database window just clear the connections than click cancel button (don’t detach the database). Close the EM and try to run the ALTER DATABASE again.

Using SQL Server Management Studio
1. Right click the related database then click properties.
2. Choose Option page
3. From Restrict Access option, select Single
4. Click OK and Open Connections dialog will appear. Click Yes if you want to close all connection connected to the database and change the database on single user mode.

Using Enterprise Manager
1. Right click the related database then click properties.
2. Choose Option page.
3. Tick Restrict Access and select Single User.
4. Click OK. If other users connect to the database, this process will be failed. You have to clear the connection manually and try again.

IdeaYou can use SQL Server Management Studio to connect to database on SQL Server 2000. Since SQL Server Management Studio is able close all connection when we try to change database to single user mode, it is more simple than we use Enterprise Manager.

Create a free website or blog at WordPress.com.