SQL Server wont start as it could not create tempdb

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

SQL Server wont start as it could not create tempdb

 

 

 

Step 2: Connected SQL Server with the SQLCMD

SQL Server wont start as it could not create tempdb

 

 

 

 

Step 3: Modify the tempdb file location, make sure you change the tempdb file size

SQL Server wont start as it could not create tempdb

 

 

 

 

Step 4: Stop the SQL Server Services

Step 5: Start the SQL Server services in normal mode

SQL Server wont start as it could not create tempdb

 

 

 

 

 

 

 

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.

Related Posts

Leave a comment