SQL Server startup options for troubleshooting

When something goes wrong with your SQL Server database and you cannot connect, it may be necessary to place it in a mode better suited for troubleshooting.  Two of these modes are single-user and minimal-configuration.

Single-user mode (-m)

This mode is used to change server configuration options or recover a damaged master database or other system database.

NOTE – Since only one user can connect to the SQL Server database in single-user mode (hence the name), it is important to stop and/or temporarily disable the SQL Server Agent service going through Start -> Administrative Tools -> Services and right-clicking on the service to change its status.  If started, this service will automatically occupy the only available connection in single-user mode, preventing further connections.  Also, the clustered service DLL will occupy the only available connection in a clustered environment, so this work around is necessary, but only in a clustered environment:

  1. Remove the –m startup parameter from the SQL Server advanced Properties (more on this process later under “Setting or removing a special startup mode in SQL Server”).
  2. Take the SQL Server service offline.
  3. From the current owner node of this group, issue the following command from the command prompt: net start MSSQLSERVER /m.
  4. Verify from the cluster administrator or failover cluster management console that the SQL Server resource is still offline.
  5. Connect to the SQL Server now using the following command and do the necessary operation: SQLCMD -E -S<servername>.
  6. Once the operation is complete, close the command prompt and bring back the SQL and other resources online through cluster administrator.

Minimal-configuration mode (-f)

This option starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-allocating memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.

Setting or removing a special startup mode parameter in SQL Server

  • Log into the server hosting the SQL Server database or log into one of the nodes hosting the SQL Server cluster.
  • Start the SQL Server Configuration Manager.  If you do not see it under the SQL Server application group in the Start menu, execute “SQLSERVERMANAGER10.msc” from the Run command window for SQL Server 2008 or “SQLSERVERMANAGER11.msc” from the Run command window for SQL Server 2012.
  • Select “SQL Server Services” in the left window pane and find the service called “SQL Server (instance)” in the right window pane where instance is the name given the named instance.
  • Right-click on the service name and select Properties.
  • In SQL Server 2012, the process is easy.  Simply go to the Startup parameter tab, type in the desired parameter in the “specify a startup parameter” box and click Add.  You can remove the parameter from “existing parameters” window under this tab and click Remove.
  • In SQL Server 2008, go to the Advanced tab, and scroll-down to Startup parameters. Click on the values on the right and use the drop-down arrow to enable a small window to add or remove parameters.
  • Restart this service to enable or disable the startup mode

 

Advertisements

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