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

3 Comments »

  1. Just dropping by.Btw, you website have great content!

    ______________________________
    All Natural Cure For Stopping Acne In 3 Days!

    Comment by Ilhan — March 3, 2009 @ 1:33 PM

  2. you write the name ‘sqlserv.exe’ and i find it as sqlservr.exe for my SQL Server 2005. Please correct it.

    Comment by Atif Shehzad — June 17, 2009 @ 12:13 PM

    • Hi! You’re absolutely correct. I’ve revised it. Many thanks.

      Comment by solihinho — June 23, 2009 @ 1:09 AM


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: