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

SQL SERVER – How to Start SQL Server Service Without tempdb? move-tempdb-01

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

SQL SERVER – How to Start SQL Server Service Without tempdb? move-tempdb-02

  1. 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

SQL SERVER – How to Start SQL Server Service Without tempdb? move-tempdb-03

  1. Stop SQL Service

SQL SERVER – How to Start SQL Server Service Without tempdb? move-tempdb-04

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)

, ,
Previous Post
SQL SERVER – Scripting Tip: PowerShell to Identify Last Bootup Time
Next Post
SQL SERVER – Boosting User Experience with Analysis Services Perspectives – Notes from the Field #116

Related Posts

9 Comments. Leave new

  • twoknightsthenight
    February 23, 2016 9:05 am

    Point #2 is very handy. The [dot] is good to know for strange machines and instances they dump on me.

    Reply
  • Nicely recorded the issue. I did the same mistake when my db is in external HDD.

    Reply
  • it’s error in command: NET START MSSQLSERVER /f
    correctly run: NET START MSSQLSERVER -f

    Reply
    • I have never tried -f command. Was there any error? I don’t know if “-f” would be honored. Did you check ERRORLOG to confirm it -f was applied during startup?

      Reply
  • @DonPedro: I run into an error, when I try to start it with -f instead of /f

    However I have an addition:
    Sometimes someone else/some service might kidnap your connection before you are able to connect to it and you get the following error message:

    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user ‘ADVENTUREWORKS\Student’. Reason: Server is in single user mode. Only one administrator can connect at this time..

    My workaround to avoid someone kidnapping your connection is starting the SQL Server with the following command:

    NET START MSSQLSERVER /f /mSQLCMD

    /mSQLCMD means that you start in single user mode (which is because of /f anyway) and only the program SQLCMD can connect. But beware, you have to write SQLCMD in capital letters, else it won’t work.

    Normally you don’t need to be afraid of someone kidnapping your connection with SQLCMD ;)

    Reply
  • Hi I am getting ODBC Driver 11 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. . error when I am trying to execute SQLCMD -s. command

    Reply
  • I had to run with trace flag NET START MSSQLSERVER /f /T3608

    Reply

Leave a Reply

Menu