This weekend while I was holding a pager, I got an interesting pager – SQL Server won’t start after a scheduled reboot. As we all do, the first attempt was to try and restart the service which failed complaining that the service did not started in timely fashion. While I was reviewing the logs found that the main reason for SQL Server wont start as it could not create tempdb.
2017-03-04 00:22:01.980 spid5s The resource database build version is 9.00.4035. This is an informational message only. No user action is required. 2017-03-04 00:22:02.120 spid5s Server name is 'SRV'. This is an informational message only. No user action is required. 2017-03-04 00:22:02.120 spid9s Starting up database 'model'. 2017-03-04 00:22:02.190 spid9s Clearing tempdb database. 2017-03-04 00:22:02.230 spid9s Error: 17053, Severity: 16, State: 1. 2017-03-04 00:22:02.230 spid9s D:\Tempdb\tempdb.mdf: Operating system error 112(There is not enough space on the disk.) encountered. 2017-03-04 00:22:02.300 Server A self-generated certificate was successfully loaded for encryption. 2017-03-04 00:22:02.300 Server Server is listening on [ 'any' <ipv4> 1433]. 2017-03-04 00:22:02.300 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. 2017-03-04 00:22:02.300 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ]. 2017-03-04 00:22:02.300 Server Server is listening on [ 127.0.0.1 <ipv4> 1434]. 2017-03-04 00:22:02.300 Server Dedicated admin connection support was established for listening locally on port 1434. 2017-03-04 00:22:02.320 Server SQL Server is now ready for client connections. This is an informational message; no user action is required. 2017-03-04 00:22:02.350 spid9s Error: 1802, Severity: 16, State: 4. 2017-03-04 00:22:02.350 spid9s CREATE DATABASE failed. Some file names listed could not be created. Check related errors. 2017-03-04 00:22:02.350 spid9s Error: 5149, Severity: 16, State: 1. 2017-03-04 00:22:02.350 spid9s MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file. 2017-03-04 00:22:02.350 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 c 2017-03-04 00:22:02.350 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
If you see here in this Error Log excerpts, it has different error messages like below:
Error: 17053, Severity: 16, State: 1 which is also one off the cause, DBCC failed due to space issue. Whenever a DBCC runs it creates an internal snapshot for a database and it needs space, when there isn’t enough space available the DBCC will fail.
Error: 1802, Severity: 16, State: 4 and Error: 5149, Severity: 16, State: 1 which means it failed to clear up and create tempdb file or files are being accessed by some other process.
At the end there is an important message for us, Error 112 (there is not enough space on the disk.). Which was the case when I received a page. Actually, there was about 10 gigs of space left on D drive and the size of the tempdb was more than 20 gigs before the SQL Server failed to start. And that is the exact error in our case.
Here are the steps I have run to resolve error SQL Server wont start as it could not create tempdb
Step 1: Did started SQL Server service with minimal configuration
Step 2: Connected SQL Server with the SQLCMD
Step 3: Modify the tempdb file location, make sure you change the tempdb file size
Step 4: Stop the SQL Server Services
Step 5: Start the SQL Server services in normal mode
And this is is how I have fixed SQL Server wont start as it could not create tempdb. And the server is working fine, later we did the permanent fix regarding the space.
Let me know if you have encounter issues like this and how did you resolved it, or you may send me the error log details on hemantgiri [at] sql-server-citation[dot]com or contact on my twitter handle http://twitter.com/ghemant and I will try to reply you over email or make a blog post out of it explaining.
