SQL Server Whitepaper

SQL SERVER WON’T START

Why SQL Server Service Won’t Start

If you are a professional SQL Server DBA, you must have faced this issue at least once in your career. Your junior admin calls you during your vacation time with the news that the production instance for some strange reason is not starting and something seems to be wrong. There could be many reasons which can cause this problem, and this paper will focus on the top 7 reasons that we have seen along with tips to resolve the issues. Here are the initial error messages which you could receive when trying to start SQL Services from various places. We will investigate reasons for receiving each of these messages and possible resolutions for them.

Service account password was changed but not updated on SQL Server

Service account password was changed but not updated on the server where SQL Server instance is installed. This is one of the most common causes where the service account password has been changed by a domain admin or SQL Admin, but this information is not updated in SQL Server Services. Here is the error which we would see if we try to start using Services. System Event logs should show this information: We need to update the password in services. The right way to do it is to use SQL Server Configuration Manager and type in the new password (under Log On tab) as shown below. That was easy, to say the least. Now let us move to the next message.

Startup Parameters have Incorrect File Path Locations

Startup parameters have incorrect file path locations. This is another common cause of SQL Server Service startup failure. Let’s assume that the master database is located on a drive and files of the database (master.mdf and mastlog.ldf) are not available. Since the master database is a system database, SQL Service would fail to start. If we try to start SQL via services, we will get this error. If we attempt the same via the configuration manager, we get a standard error which doesn’t explain much. Whenever we get such errors, we should start looking at SQL Server ERRORLOGs that are defined under start-up parameters or application event log. We can look at SQL Server Configuration Manager and look for the Startup parameter having a name -e as shown below (for SQL 2017): We can open ERRORLOG using notepad or any other text editor. Here is the snippet which shows the problem. As highlighted above, we can see that SQL is not able to find the file master.mdf. Below is another example of an error for the model database. <Date Time> spid9s Error: 17207, Severity: 16, State: 1. <Date Time> spid9s occurred while creating or opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\modellog.ldf’. Diagnose and correct the operating system error, and retry the operation. <Date Time> spid9s File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\modellog.ldf” may be incorrect. <Date Time> spid9s Error: 945, Severity: 14, State: 2. <Date Time> spid9s Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. <Date Time> spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized. First we need to find out the correct location of the files. Once files are identified, either put them into the location where SQL Server wants or ALTER the database to point to the correct location. If there is an issue with the model or TempDB database, then we need to start SQL Server using trace flag 3608 as explained here.

Database Files Missing Due to Accidental Deletion or Disk Failure

If files are missing or corrupted for system databases (master and model), the SQL Server service would not start. ERRORLOG (mentioned earlier) would contain the exact database name and file name which has the problem. Here are few snippets of error text pointing to the corruption of system databases. <Date Time> spid5s Starting up database ‘master’. <Date Time> spid5s Error: 9003, Severity: 20, State: 1. <Date Time> spid5s The log scan number (216:72:1) passed to log scan in database ‘master’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem fails during startup. <Date Time> spid5s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online. Depending on which database file is corrupted, we need to take appropriate action. If master database files are corrupted (error above), then we need to rebuild the master database and restore it from the backup. If the issue exists with other system databases, then SQL can be started via trace flag, and they can be restored. Books online have a topic for each database. Refer to this article.

The TempDB Database Was Moved Incorrectly

Moving the TempDB database is not a common activity. Sometimes a DBA might make a mistake while doing an ALTER DATABASE for the TempDB database. If SQL Server is not able to start system databases (master, model, and TempDB) correctly, the service startup will fail. Let us assume we have moved TempDB to E:\TempDB using the below command while SQL is running. Once the command is executed, we will receive the message below. Now, if we restart SQL and assuming the configured path is invalid, we will get errors which we mentioned earlier. Here is the message output in our SQL Server ERRORLOG: <Date Time> spid11s Clearing tempdb database. <Date Time> spid11s Error: 5123, Severity: 16, State: 1. <Date Time> spid11s CREATE FILE encountered operating system error 3 (The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\tempdb.mdf.’ <Date Time> spid11s Error: 17204, Severity: 16, State: 1. <Date Time> spid11s FCB::Open failed: Could not open file E:\TempDB\tempdb. mdf for file number 1. OS error: 3(The system cannot find the path specified.). <Date Time> spid11s Error: 5120, Severity: 16, State: 101. <Date Time> spid11s Unable to open the physical file “E:\TempDB\tempdb. mdf”. Operating system error 3: “3(The system cannot find the path specified.)”. <Date Time> spid11s Error: 1802, Severity: 16, State: 4. <Date Time> spid11s CREATE DATABASE failed. Some file names listed could not be created. Check related errors. <Date Time> spid11s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized. There are two ways to fix the problem of the TempDB path.

• Either create the path which is shown in the error log.
• Or start SQL Server using “f” parameter and ALTER DATABASE for TempDB and point to correct path as shown below.
  • Start SQL with /f using net start.
  • Connect to SQL via SQLCMD.
  • ALTER tempdb database.
  • Stop SQL.
  • Start SQL normally.

The Model Database Has Become Corrupted

Below are the various errors related to model database problems. They are taken from ERRORLOG file: Database Files missing: <Date Time> spid10s Starting up database ‘model’. <Date Time> spid10s Error: 17204, Severity: 16, State: 1. <Date Time> spid10s FCB::Open failed: Could not open file E:\ <Date Time> spid10s Error: 5120, Severity: 16, State: 101. <Date Time> spid10s Unable to open the physical file “E:\Database File level Corruption: <Date Time> spid11s Error: 824, Severity: 24, State: 2. <Date Time> spid11s SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:32; actual 4096:0). It occurred during a read of page (1:32) in database ID 3 at offset 0x00000000040000 in file ‘E:\Program Files\Microsoft SQL Server\MSSQL14. SQL2017\MSSQL\DATA\model.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. <Date Time> spid11s Error: 945, Severity: 14, State: 2. <Date Time> spid11s Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. These are easy to fix provided we have not created any objects inside the model database. There are multiple ways to fix the startup problem: The Port is Being Used by Another SQL Instance on the Same Machine. Changing the port of a SQL instance is not a day-to-day task but in some situations SQL Server startup might fail with these errors in ERRORLOG: <Date Time> spid15s Error: 26023, Severity: 16, State: 1. <Date Time> spid15s <Date Time> spid15s Error: 17182, Severity: 16, State: 1. <Date Time> spid15s TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted. <Date Time> spid15s Error: 17182, Severity: 16, State: 1. <Date Time> spid15s TDSSNIClient initialization failed with error 0x2740, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Only one usage of each socket address (protocol/network address/port) is normally permitted. <Date Time> spid15s Error: 17826, Severity: 18, State: 3. <Date Time> spid15s Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log. <Date Time> spid15s Error: 17120, Severity: 16, State: 1. <Date Time> spid15s SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems. Highlighted above is the actual problem of startup failure. From the command line, we need to find out which process is using port 1433. So first we can use netstat-on and find process id which is listening on that port. Then use tasklist to find which executable has the process id (found in the previous step). Here is a sample output: In this case, there is already another SQL Server Instance running and holding port 1433. To solve this, either change the port of that process or change the port of the SQL Server which is not getting started.

Simple Errors

If you are reading this reason, there is a good chance that you have not encountered any of the above errors. This means you have an error which may qualify as a simple error. Here are a few of the errors which are very simple and can be resolved very quickly. As we wrap up, we have seen 7 different reasons why the SQL Server Service can be unresponsive. Each of these reasons has a different method to resolve the issue, but all of them are easy to address if you know what to look for

SQL SERVER DISCOVERY AND TUNING SUITE

This helpful tool suite includes three key IDERA products for complete SQL Server discovery and tuning, from monitoring and diagnostics to discovery, tracking, and managing inventory to finding and fixing index fragmentation:

SQL Diagnostic Manager Pro

Find and fix database performance problems with monitoring, alerting, and diagnostics.

SQL Inventory Manager

Discover, track, and manage your SQL Server inventory and assets.

SQL Defrag Manager

Find and fix SQL index fragmentation issues.