Have you ever encountered a situation where your SQL Server instance refuses to start? It can be a frustrating experience, especially if you are in the middle of a presentation or working on an important project. In this blog post, we will discuss a common issue that can prevent SQL Server from starting and how to fix it.

Before we dive into the solution, let me emphasize that the steps mentioned here should only be performed on non-production environments. Making changes to your production environment without proper testing and approval can lead to unwanted consequences.

So, what could be the reason behind SQL Server not starting? One possible cause is a misconfiguration related to the location of the TempDB database files. TempDB is a system database in SQL Server that is used to store temporary objects, such as temporary tables and variables.

In a recent incident, I faced a similar issue while experimenting with my local SQL Server instance. I was about to deliver a session on SQL Server when I discovered that I couldn’t connect to the SQL Server using SQL Server Management Studio (SSMS). Panic set in as I tried to figure out the cause of the problem.

Upon checking the error logs, I found the following error message:

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

From the error message, it was clear that SQL Server was unable to find the specified path for the TempDB database files. Upon further investigation, I realized that I had been using a USB drive for a demo the previous night, and it seemed to be the culprit behind the issue.

To resolve this problem, I needed to change the file location of the TempDB database. Here are the steps I followed:

1. Start SQL Server in minimal configuration using the startup parameter /f. This can be done by running the following command in the command prompt:

NET START MSSQLSERVER /f

2. Connect to SQL Server using SQLCMD. Open the command prompt and run the following command:

SQLCMD -S .

Note: If you are using a default instance on your local machine, you can use a period/dot symbol as the server name.

3. Run the ALTER DATABASE command to modify the file locations of the TempDB database:

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

4. Stop the SQL Server service.

After following these steps, I was able to successfully move the TempDB files to a valid location, and SQL Server started without any issues.

It is important to note that incidents like these can happen by mistake, and it is crucial to be cautious when making changes to your SQL Server environment. If you are a presenter for an upcoming event, it is advisable not to modify your settings the night before. Always double-check your demos in the morning to ensure everything is in order.

Remember, some lessons are learned the hard way, while others come from experience. If you have encountered similar problems or have any questions, feel free to share your thoughts.