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.

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

Here is step by step direction to do the same.

Solarwinds

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

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

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

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

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.

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

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)

Solarwinds
, , ,
Previous Post
How to Grow All Files in a File Group Equally? – TRACE FLAG 1117 – Interview Question of the Week #201
Next Post
How to Shrink All the Log Files for SQL Server? – Interview Question of the Week #203

Related Posts

Leave a Reply

Menu