INF: Hey, my SQL Server Service is not starting – what do I do?

This is one of those challenging situations when customers call us at times. I can understand the gravity of the situation – your boss is behind your back, your business is down, your application is down, your website cannot accept orders or your phone is ringing off the hook with calls from the helpdesk. Don’t worry; I will guide you through some basic steps & actions that you can take to make some sense amidst all this madness.

Warning:

 

Nothing in life is to be feared. It is only to be understood. - Marie Curie ******

******

Startup Options
Have you ever called SQL CSS for a startup issue and seen the engineer use some cool trace flags or parameters to get SQL started? Well, it’s all documented (well, most of it that’s worth knowing)

**Using the SQL Server Service Startup Options
**https://msdn.microsoft.com/en-us/library/ms190737.aspx

The most common ones by far that I use on SQL startup issues are**:-**

Sr.no	Parameter	Description
1.	-c	Start as a console application, not as a service.
2.	-m	Tries to start the SQL service in single user mode, i.e. only a single user can connect. This single user connection can be either  a sysadmin or a regular user connection
3.	-f	Tries to start the SQL service in Minimal configuration mode. This implicitly puts SQL Server in single-user mode and this also means only the system databases master, model, tempdb & mssqlsystemresource are recovered and started.
4.	-T XXXX	Tries to start the SQL Server should be started with the specified trace flag which follows after -T. Again this is case sensitive.
5.	-g	Specifies the number of megabytes (MB) of memory that SQL Server leaves available for memory allocations within the SQL Server process, but outside the SQL Server buffer pool. The default value for this is 256MB.
6.	-m”ClientApp Name”	You can limit the connections to the specified client application. For example, -m"SQLCMD" limits connections to a single connection and that connection must identify itself as the SQLCMD client program. You can use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. Cool option J
7.	-k  123	Limits the number of checkpoint I/O requests per second to the value specified e.g. 123 MB/sec. Refer https://support.microsoft.com/kb/929240 for more info.

 

Trace Flags
For more information some common trace flags see here https://msdn.microsoft.com/en-us/library/ms188396.aspx. Few of the common ones that come handy during startup issues are:

1. 3608 à Starts SQL and recovers only the master database.
2. 4010 à Allows only shared memory connections to the SQL Server. Meaning, you will only be able to connect from the server machine itself. Client connections over TCP/IP or named pipes will not happen.
3. 4606 à Disables password policy check during server startup.
4. 4022 à Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting "scan for startup procs" configuration option to 0.

Here are the steps to start SQL Server from command prompt:-

1. ****Right-click on the SQL Server service name in services.msc 

   

2. Copy the path that you see here.

3. Open a Command Prompt window and navigate to the path from step2. ****

E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -m -sSQL2005 ** ** 

Note:

A common mistake that people sometime make is to try and start SQL using sqlservr.exe and you get this error,

Text: “Your SQL Server installation is either corrupt or has been tampered with. Please uninstall then re-run setup to correct this problem”

Okay, you see this message and you are thinking **oh no, there goes my dinner plans!
Timeout!
**The above message can be misleading at times. You will get the above error if you try to start SQL server from command prompt for a named instance and do not specify an instance name. Without the -s switch the default instance will try to start, but using the sqlservr.exe file from another instance binn folder. You will also get this error if you specify an invalid parameter to sqlservr.exe. So, the installation is not corrupt after all (Dinner plans back on?)

Here’s how to start a named instance,
sqlservr.exe -c -s INSTNAME

Here’s how to start a default instance,
**sqlservr.exe -c

**Here’s how to start a named instance recovering only master database
**sqlservr.exe -c -sMATRIX –T3608

**

Note:

Some Common Scenarios for Startup Failures

Scenario 1

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log.
                                 OR
“The service failed to respond in a timely fashion”.
                                 OR
“The service failed to start”.

1. Locate the SQL Errorlog as that is the 1^st place to look if a SQL service is not starting.
2. This can be done by looking up the -e startup parameter value for the SQL Server instance in question. 
   
3. Verify that the files pointed to by -d (master data file location),  -e (Errorlog location) and  -l (master log file location) actually exist and have the correct file paths and that the SQL service startup account has proper permissions.

Alternately you can also get this from the registry by looking at the values SQLArg0, SQLArg1 and SQLArg2 in the following locations.

SQL 2000
Default: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
Named: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INSTANCENAME\MSSQLServer\Parameters

SQL 2005
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters

SQL 2008/R2
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\Parameters

 4. Once you have located the errorlog open it in notepad and look for any errors. An example would be like this,

2010-11-20 07:50:58.250 Server Error: 17113, Severity: 16, State: 1.
2010-11-20 07:50:58.250 Server Error 2(The system cannot find the file specified.) occurred while opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

The above message clearly indicates that the master.mdf was not found in the location it was expecting it to be found.

 

Scenario 2

You can also look into the Windows Application log for any events related to SQL Server.

initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG1\ERRORLOG'. Operating system error = 3(error not found).

The above error indicates that SQL could not find the errorlog file in the above location. What does this mean? This means that since SQL could not find the errorlog it has no place to log messages, hence it did not start.

When you are faced with this error, then if you look into the errorlog location, you will notice log files whose date/time stamp is not current. Do not make the mistake of opening these files and troubleshooting based on messages that are not for the current startup failure. This is where looking into the application log gives you the current entries.

I hope cleared up any confusion you might have had regarding this rule, the warning status and the simple logic behind the rule. As always stay tuned for more SQL tips…

Scenario 3

2011-04-16 07:52:49.32 spid5s      Error: 17204, Severity: 16, State: 1.
2011-04-16 07:52:49.32 spid5s      FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf for file number 2.  OS error: 32(error not found).

OS Error 32 translates to “The process cannot access the file because it is being used by another process.” How did I know this? Simple. You can find the text for any windows error by running this from a command prompt à net helpmsg XXX, where XXX is the error number

In the above scenario, some other process/application has a lock on master.mdf and this isn’t allowing SQL to take access, hence the service fails to start. To find out which process it is you can use either Process Explorer or Handle.exe from sysinternals. The usual culprits are Anti-Virus or Anti-Malware services. Once you identify the process, after stopping that, attempt to start SQL again. If it fails, verify from the errorlog that the error code is still 32 and has not changed. Most people miss this trick and assume the error is the same after making some changes. Don’t take it for granted, since the error might have changed but the service still doesn’t start. This indicates that the changes done have been successful.

 Some Common Causes for SQL Service startup failures

• Access denied on the folders for the SQL Server Service Account, specifically on the DATA folder which contains the SQL Server system databases.
• Due to some other processes like anti-virus holding a lock on the SQL database files.
• Insufficient permissions granted to the SQL Server Service account.

Important:

 

• Server side protocols for SQL not set correctly or disabled.
• Critical system database files not found due to either accidental deletion of files or disk failures.
• System database files having inconsistencies preventing SQL Server startup.
• Password of the service account was changed but not updated on the server that has the SQL instance installed.
• Startup parameters have incorrect file path locations.
• The SQL Server service is disabled in Service Control Manager.

Critical:

**

**

Additional Resources

**Error Messages of SQL Server 2005 Start Up Failure
**https://blogs.msdn.com/b/sql_protocols/archive/2006/04/28/585835.aspx

An error occurred during encryption
https://blogs.msdn.com/b/blakhani/archive/2009/11/24/sql-server-2005-express-setup-failure-with-error-an-error-occurred-during-encryption.aspx

---

 

Regards,
**Sudarshan Narasimhan
**Technical Lead,
SQL Server Support Team

Comments

• Anonymous
  April 09, 2012
  Superb Explanation Sudarshan, whats the difference between SQL server 1) Service Failure 2) DB Failure 3) Instance failure Thanks in advance.

• Anonymous
  November 22, 2012
  Very nice and detailed information for MSSQLServer service not starting. Thanks for this.

• Anonymous
  January 24, 2013
  Good one.

• Anonymous
  April 09, 2013
  good one...

• Anonymous
  October 24, 2013
  We have same issue.but when we checked in Bin folder sqlservr.exe file itslef removed from other team..Is it possible to recover..?

• Anonymous
  October 24, 2013
  Atchi, You can copy exe from some other server having same version as this SQL. I am not sure which other files are missing. Reinstalling SQL might be something you should do.

• Anonymous
  November 23, 2013
  I have a connection error "Server does not exist or access denied" Can you help?

• Anonymous
  February 07, 2014
  Nice blog Balmukund

• Anonymous
  February 07, 2014
  Great source of information..Thanks for sharing

• Anonymous
  February 09, 2014
  Nice Article with detialed version !!

• Anonymous
  March 05, 2014
  Fantastic information. Thank you so much!

• Anonymous
  September 02, 2014
  What to do when an inplace upgrade renders sql instance as inactive and there is no folder in registry which points to initial sql instance. Eg: say I want to upgrade SQL 2012 to SQL 2014. Now when I go to registry I find that in Instances SQL points to MSSQL11.MSSQLSERVER and Inactive points to MSSQL12.MSSQLSERVER, but the folder existing is only MSSQL12.MSSQLSERVER and folder MSSQL11.MSSQLSERVER does not exist. Repair does not identify Database engine and only shared features are repaired. Upgrade does not find any feature to be upgraded. If you go ahead with uninstall using msiexec for inactive instance, database engine is gone. What to do in such a case?

• Anonymous
  January 04, 2017
  I had a problem to my failover server similar to the scenario 2 - ‘R:\MSSQL10.MSSQLSERVER\MSSQL\Log1\ERRORLOG’. Operating system error = 3(The system cannot found the path specified). But it is running on window server 2003, any suggestion on how to trouble shoot?