SQL SERVER – How to Start SQL Server Service Without tempdb?

Before I start explaining the situation, I got into while experimenting with the SQL Server instance on my machine, let me air a word of caution – Please DONOT try this on your production environments. Unless you want to grab unwanted attention from your seniors, this is not something I would want you to ever experiment. In a recent configuration change on my local instance, I was facing an unique issue as I was standing in front of an audience for a SQL Server based session. Rarely does it happen that I get into Murphy’s law of demo not working in front of an audience because I carry multiple backups. Interestingly, this time when I tried to connect to the SQL Server using SSMS, it just refused to connect. I was completely taken aback and what happened after that is a different story about how to start SQL Server Service.

On my way back to the hotel, I just couldn’t stop myself from thinking to a possible reason why my SQL Server was not starting. SQL Server provided many good ways to fix a problem. It is just then I realized I was playing with a USB drive for a demo the earlier night and was curious to understand if that was the culprit. I immediately got to the error logs to check for clues:

2016-02-19 00:44:05.57 spid6s      Clearing tempdb database.
2016-02-19 00:44:05.57 spid6s      Error: 5123, Severity: 16, State: 1.
2016-02-19 00:44:05.57 spid6s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘F:\TempDB\Tempdb.mdf’.
2016-02-19 00:44:05.61 spid6s      Error: 17204, Severity: 16, State: 1.
2016-02-19 00:44:05.61 spid6s      FCB::Open failed: Could not open file F:\TempDB\Tempdb.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2016-02-19 00:44:05.61 spid6s      Error: 5120, Severity: 16, State: 101.
2016-02-19 00:44:05.61 spid6s      Unable to open the physical file “F:\TempDB\Tempdb.mdf”. Operating system error 3: “3(The system cannot find the path specified.)”.
2016-02-19 00:44:05.61 spid6s      Error: 1802, Severity: 16, State: 4.
2016-02-19 00:44:05.61 spid6s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2016-02-19 00:44:05.61 spid6s      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.

When I looked at the drives available I saw below

Since I don’t have F drive now, TempDB can’t be created and SQL is not starting. So, I have to change tempDB database’s file location.  I already have a blog on doing this

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.

I tried trace flag 3608 but it did not work as ALTER was failing. Below worked for me.

1. Start SQL Server in minimal configuration using startup parameter f

NET START MSSQLSERVER /f

2. Connect to SQL via SQLCMD

SQLCMD -S .

(I have used period/dot symbol because it’s a default instance on local machine)

And run ALTER command

USE MASTER
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'D:\TempDB\Tempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'D:\TempDB\templog.ldf')
GO

3. Stop SQL Service

After above steps I was able to move tempdb files to a valid location and SQL started successfully. I know such things happen by mistake and we are bound to get into problems. As I wrap up, I would like to give a warning because if you are a presenter for the event coming up, please don’t mess with your settings the night before. It is always advisable to check your demo’s the morning once to see if things are in order. Some lessons are learnt the hard way, some by experience. Do let me know if you ever got into such problems.

Reference: Pinal Dave (https://blog.sqlauthority.com)