Under certain circumstances, we may have to start an instance of SQL Server in single-user mode by using the start up option -m.
For example, if we want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.
When we start an instance of SQL Server in single-user mode, note the following:
Only one user can connect to the server.
The CHECKPOINT process is not executed. By default, it is executed automatically at startup.
Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
To open the SQL Server instance in SIngle user mode just Go to SQL Server Configuration Manager and click on SQL Server Services i.e. By default it is selected.
Now Click on desired SQL Server instance to which you want to open in single user mode. Then right click go to properties.
Click on Startup Parameter. (In sql server 2005/2008 click On the Advance tab enter param ‘-m;‘ before existing params in Startup Parameters box. Make sure that you entered semi-comma after -m.)
Put -m in text box and then click on Add. Click on OK.
A warning window will appear. Click ok then restart the service.
Now lets connect it through SSMS. You can also connect it through command line.
Ooops!!! We got an error. Saying Sql server is in single user mode and only one administrator can connect at this time.
Ohhh yesss We just forgot to stop SQL Server Agent service. Now stop this service and try again to connect it through new query editor it will be successful. If you will connect through explorer then after that you will not be able to open a query window.
Once done remove -m from startup parameter and restart the service.
For example, if we want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.
When we start an instance of SQL Server in single-user mode, note the following:
Only one user can connect to the server.
The CHECKPOINT process is not executed. By default, it is executed automatically at startup.
Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
To open the SQL Server instance in SIngle user mode just Go to SQL Server Configuration Manager and click on SQL Server Services i.e. By default it is selected.
Now Click on desired SQL Server instance to which you want to open in single user mode. Then right click go to properties.
Click on Startup Parameter. (In sql server 2005/2008 click On the Advance tab enter param ‘-m;‘ before existing params in Startup Parameters box. Make sure that you entered semi-comma after -m.)
Put -m in text box and then click on Add. Click on OK.
A warning window will appear. Click ok then restart the service.
Now lets connect it through SSMS. You can also connect it through command line.
Ooops!!! We got an error. Saying Sql server is in single user mode and only one administrator can connect at this time.
Ohhh yesss We just forgot to stop SQL Server Agent service. Now stop this service and try again to connect it through new query editor it will be successful. If you will connect through explorer then after that you will not be able to open a query window.
Once done remove -m from startup parameter and restart the service.
Thanks!!!
References: Microsoft BOL
No comments:
Post a Comment