In SQL Server, the tempdb database plays a crucial role in storing temporary objects, such as temporary tables and variables, as well as managing sorting and hashing operations. In some cases, the tempdb database may become damaged or inaccessible, causing the SQL Server instance to fail to start. However, it is possible to rebuild the tempdb database separately without rebuilding other system databases.

Identifying the Issue

If the SQL Server fails to start due to an issue with the tempdb database, you may encounter an error message indicating the failure. To understand the cause of the problem, you can open the ERRORLOG file, which provides detailed information about the failure. In some cases, the failure may be due to the unavailability of the file path for the tempdb files.

Rebuilding the Tempdb Database

To rebuild the tempdb database, you need to start the SQL Server from the command prompt with specific parameters. First, locate the sqlservr.exe file on your system. You can do this by searching for it in the Windows search. Once you have located the file, navigate to its directory in the command prompt using the “cd” command.

Next, start the SQL Server with the following options:

cd C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn
sqlservr -c -f -T3608 -T4022 -s MSSQLSERVER -mSQLCMD

These options have specific meanings:

• -c: Shortens the startup time of SQL Server from the command prompt.
• -f: 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 the model database is recovered and TempDB is created.
• Trace Flag 4022: Disables automatic execution of stored procedures when SQL Server starts.
• -s MSSQLSERVER: Specifies the instance name (default in this case).
• -mSQLCMD: Allows only sqlcmd to connect to the instance.

After starting the SQL Server with these options, connect to the SQL Server via sqlcmd and run the following command to reset the status of tempdb:

exec master..sp_resetstatus tempdb

It is important to keep the command prompt window open during this process. Once the command is completed, press Ctrl+C in the command prompt to shut down the SQL Server.

Starting the SQL Server Instance

If you start the SQL Server instance from SQL Server Configuration Manager after rebuilding the tempdb database, new tempdb files will be created in the default location, and the instance will successfully start. If you want to move the tempdb files to a new location, you can use either sqlcmd or SQL Server Management Studio to specify the new paths.

By following these steps, you can rebuild the tempdb database without affecting other system databases. This approach is particularly useful when only the tempdb files are damaged or missing, causing the SQL Server instance to fail to start.

Remember to always have a backup of your system databases before attempting any database recovery or rebuilding process.

Article Last Updated: 2021-10-26