Starting SQL Server in Single Mode 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.

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.