How to Rebuild the SQL Server Tempdb Database

By: Sergey Gigoyan   |   Updated: 2021-10-26   |   Comments   |   Related: More > System Databases

Problem

In one of our previous articles, we discussed how to rebuild the SQL Server system databases. However, sometimes it is not needed to rebuild all system databases. If tempdb is damaged, for example, it is possible to rebuild it without rebuilding other system databases.

In this tutorial, we will illustrate how to recover the tempdb database. One of the common cases, when tempdb recovery is needed, is that the location of tempdb files becomes unavailable. As SQL Server creates the tempdb each time it starts, the case that the SQL Server engine does not start due to corrupted tempdb files is rare. Therefore, we will focus mainly on the first case (when the file path is not available), but we will describe the whole process of rebuilding tempdb.

Solution

Let's demonstrate an example when a SQL Server instance fails to start due to the issue with the tempdb database.

In SQL Server Configuration Manager, we right-click on the corresponding SQL Server service and choose "Start". We receive the following error message:

From the message it is clear that the SQL Server failed to start, but the reason for this failure is not mentioned. To understand the cause of this problem, we open the ERRORLOG file (as suggested in the message). There we can see that it was not possible to create the tempdb database, therefore, the instance could not start:

In our example, the path of the tempdb files has become unavailable for some reason. In order to specify new paths for the tempdb database files, we need to start and connect to the instance, but as we can see, we cannot do that. So, we will rebuild the tempdb database. To do that, we should start the SQL Server (sqlservr.exe) from the command prompt with specific parameters.

We can find the location of the sqlservr.exe file by typing its name in the Windows search:

After locating the file and path, we will move to its directory in the command prompt using the "cd" command. Once we are in the right directory, we will start SQL Server with specific options shown below:

cd C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn

sqlservr -c -f -T3608 -T4022 -s MSSQLSERVER -mSQLCMD

As we can see, we used special options while starting the SQL Server. Here is information about these options from the Microsoft documentation.

• The -c parameter shortens the startup time of SQL Server from starting from command prompt.
• The -f parameter starts up SQL Server with minimal configuration.
• Trace Flag 3608 - "Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require TempDB are initiated, then model is recovered and TempDB is created ".
• Trace Flag 4022 - "Disables automatic execution of stored procedures when SQL Server starts."
• The -s parameter specifies the instance name which is the default in our case.
• The -mSQLCMD parameter is used to allow only sqlcmd to connect to the instance.

Then, we need to connect to the SQL Server to reset the tempdb status. It is very important to note that we should leave this command prompt window open during the process. Otherwise, the process will be terminated.

Now, without closing the command prompt window, we connect to the SQL Server via sqlcmd and run the following command to reset the status of tempdb:

exec master..sp_resetstatus tempdb

After this command is completed, in the command prompt we press Ctrl+C to shut down SQL Server:

Now, if we start the SQL Server instance from SQL Server Configuration Manager, new tempdb files will be created in the default location and the instance will successfully start:

If we want to move the tempdb files to a new location, we can use either sqlcmd or SQL Server Management Studio to specify the new paths. Below I have changed the paths using sqlcmd.

Once the new paths are specified, we can restart the SQL Server instance and the files will get created in the new location we specified. Thus, we have rebuilt the tempdb database without touching the other system databases.

Conclusion

To sum up, it is possible to rebuild the tempdb database separately, without rebuilding other system databases. It can be useful when only the tempdb files are damaged or missing and the instance fails to start.

Next Steps

For additional information, please follow the links below:

• Rebuild the tempdb Database - Microsoft
• Rebuild System Databases - Microsoft
• Five facts about the SQL Server Tempdb Database
• Rebuild System Databases in SQL Server
• Move SQL Server System Database Files - Part 1
• DBCC TRACEON - Trace Flags (Transact-SQL) - Microsoft
• tempdb database - Microsoft
• SQL Server Single User Mode Connection with SQL Server Management Studio

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips