NOTE: This content has been updated and expanded in a whitepaper:
7 Reasons Your SQL Server Service Won't Start, by Pinal Dave
———-
After our previous blog on SQL Server Service is not responding, there were few who wanted to know if there were more reasons. In this blog we will explore 3 more reasons why the service cannot respond. In earlier blog post, we saw three major reason of SQL Service startup failure due to below:
-
Reason # 1: Service account password changed but not updated on the server where SQL Server instance is installed
-
Reason # 2: Startup parameters have incorrect file path locations.
-
Reason # 3: System database files not available – accidental deletion or corruption due to disk failures.
In this blog, we will discuss three more reasons why we can get the below error:
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”.
Reason # 4: TempDB database moved incorrectly
Moving TempDB database is not a common activity. Sometime DBA might make mistake while doing ALTER DATABASE for TempDB database. If SQL Server is not able to start system databases (master, model and TempDB) correctly, the service startup would fail.
Let us assume we have moved TempDB to I:TempDB using below command while SQL is running.
ALTER DATABASE TEMPDB
MODIFY FILE (NAME = tempdev, FILENAME = 'E:TempDBtempdb.mdf')
Once the command is executed, we will received below message.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
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:TempDBtempdb.mdf'.
<Date Time> spid11s Error: 17204, Severity: 16, State: 1.
<Date Time> spid11s FCB::Open failed: Could not open file E:TempDBtempdb.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:TempDBtempdb.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.
Solution
There are two ways to fix the problem of TempDB.
-
Either created the path which is shown in errorlog
-
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
-
Reason # 5: Model Database Corrupted.
Below are the various error 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:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAmodel.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
<Date Time> spid10s Error: 5120, Severity: 16, State: 101.
<Date Time> spid10s Unable to open the physical file "E:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAmodel.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
<Date Time> spid10s Error: 945, Severity: 14, State: 2.
<Date Time> spid10s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
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 FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAmodel.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:
-
Restore database from the backup taken on the same server. This required SQL to be started via trace flag 3608 and 3609 so that system databases are not recovered.
-
Get the MDF and LDF file of the database from different server having same build version as the current server with startup issue.
-
Repair the SQL Instance – again we would lose the object created earlier.
-
Rebuild all system databases.
Reason # 6: Port used by another SQL Instance on same machine
Changing port of SQL instance is not a day-to-day task but in some situations SQL Server startup might fail with below error in ERRORLOG:
<Date Time> spid15s Error: 26023, Severity: 16, State: 1.
<Date Time> spid15s Server TCP provider failed to listen on [ ‘any’ <ipv6> 1433]. Tcp port is already in use.
<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 is the actual problem of startup failure.
Solution:
From command line we need to find out which process is using port 1433. So first we can use
netstat -aon and find process id which is listening on that port. Then use tasklist to find which executable is having the process id (found in 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, there are two ways. Either change the port of that process or change the port of SQL Server which is not getting started.
Conclusion
As we wrap up, we have seen close to 6 different reasons why SQL Server Service can be unresponsive. Each of these reasons have a different method to resolve. Hope these blog posts were a refresher to anyone getting into these errors.