How to Start Database in a Single User Mode? – Interview Question of the Week #202

Question: How to Start Database in a Single User Mode?

Answer: We all needed one time or another time this particular help. Whenever there is a corruption or any issue with SQL Server where we need to stop access from external users/system. We have to start the SQL Server in a single user mode and there is a pretty simple trick to do the same.

Here is step by step direction to do the same.

First of all, go to SQL Server 2017 Configuration Manager.

Next right click on the SQL Server services and go to properties.

While you are at SQL Server Services Properties go to StartUp Parameters and add the startup parameter you want to add when you want to start your SQL Server. In our case we have to specify the option -m as that instructs the SQL Server to start in a single user mode.

Please make sure that after you restart the SQL Server in a single user mode. Do not open SQL Server Management Studio (SSMS) as it will open multiple connections to your database which will eventually not let you connect to your SQL Server.

You should either PowerShell or SQLCMD to connect to your SQL Server once you start it in a single user mode.

Reference: Pinal Dave (https://blog.sqlauthority.com)