Tempdb is typically configured after the installation of SQL Server for better performance. The tempdb database is rebuilt by SQL Server at startup time. In case, it is not able to rebuild the tempdb SQL Server will not be restarted. In this event there are two steps involved which are troubleshooting and recovering steps.
We don’t have the luxury of using SQL Server tools as SQL Server service will not have started. However, you can view the error log from notepad or other relevant tools.
Error log file is available at <System Drive>:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log
MSSQL11 is for SQL Server 2012 and MSSQLServer is for the default instance. In case you have a named instance called QA in SQL Server 2008, this folder will be MSSQL10.QA. In the relevant folder, open the latest error log file using Notepad. This is the error log, you will see from the SQL Server Management Studio as well. The below error can be seen in the error log file.
2015-12-08 12:46:44.99 spid10s Error: 17053, Severity: 16, State: 1. 2015-12-08 12:46:44.99 spid10s C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.MDF: Operating system error 112(There is not enough space on the disk.) encountered. 2015-12-08 12:46:45.06 spid10s Error: 823, Severity: 24, State: 6.
From the above error, it is obvious that there is not enough disk space for tempdb to rebuild. Now the question is, how to relocate the tempdb data file since SQL Server is not starting?
There is an option in SQL Server to start SQL Server with minimum configuration. Open the command line preferably with administrator permissions or run as administrator. Locate the SQLServer exe in the folder. In my case of <System Drive>:\ Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
Following are the options available for SQL Server.
[-c] (not as a service)
[-d file] (alternative master data file)
[-l file] (alternative master log file)
[-e file] (alternate errorlog file)
[-f] (minimal configuration mode)
[-m] (single user admin mode)
[-g number] (stack MB to reserve)
[-k <decimal number>] (checkpoint speed in MB/sec)
[-n] (do not use event logging)
[-s name] (alternate registry key name)
[-T <number>] (trace flag turned on at startup)
[-x] (no statistics tracking)
[-y number] (stack dump on this error)
[-B] (breakpoint on error (used with -y))
[-K] (force regeneration of service master key (if exists))
[-v] (list version information)
-f and –c are the option we need to consider. In case you have a named instance, you need to specify the named instance with the –s option.
sqlservr -s MSSQLSERVER -f –c
The below screen will be seen after executing the above command,
Since we have specified the –c option SQL Server started and is not running as a service. Let us try to connect to SQL Server Instance using SSMS. You will end up with following error.
Login failed for user XXX. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
This means that we are not able to connect from SSMS as the instance is running in single user mode. Thus, connecting to the instance using SQLCMD, is an available option. Then drop tempdb secondary files or reduce tempdb size by running necessary T-SQL commands.
Then stop the above sql server instance by pressing Ctrl+C. Now you should be able to start the SQL Server service without any issue.
]]>