How to Shrink TempDB in SQL Server with a Script

Learn how to Shrink TempDB in SQL Server with a Script. Our SQL Server Support team is here to help you with your questions and concerns.

How to Shrink TempDB in SQL Server with a Script

Did you know that managing the size of the `TempDB` in SQL Server is important when facing space constraints or performance issues?

Our experts have put together this step-by-step guide on how to shrink the `TempDB` database. Before we begin, stop the SQL Server instance.

Then, start the SQL Server instance in minimal configuration mode and follow these steps:

1. First, open a command prompt.
2. Then, change to the directory where SQL Server is installed.

   cd C:\Program Files\Microsoft SQL Server\MSSQL VersionNumber.InstanceName\MSSQL\BinnCopy Code

   Replace VersionNumber and InstanceName with the version and instance name.
3. For a named instance of SQL Server, run the following command:

   sqlservr.exe -s InstanceName -c -f -mSQLCMDCopy Code
4. Run this command for the default instance of SQL Server:

   sqlservr -c -f -mSQLCMDCopy Code
5. Now, connect to SQL Server with `sqlcmd` and run the following Transact-SQL commands.

   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = );
   GO
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = );
   GO
   Copy Code

   Replace target_size_in_MB with the desired size in megabytes.
6. After that we have to restart the SQL Server.

   So, stop SQL Server by pressing `Ctrl+C` at the command prompt window. Then, restart SQL Server as a service and verify the size of `tempdb.mdf` and `templog.ldf` files.

When users only run `Shrinkfile`, they may not achieve the desired results. However, using the `DROPCLEANBUFFERS` command can be effective. Although, `DROPCLEANBUFFERS` will clear the procedure cache. This may slow down the system temporarily as the cache rebuilds.

Our experts recommend shrinking the `TempDB` only when running out of space or in critical situations. If restarting services to shrink the database, consider running `DBCC FREEPROCCACHE`.

Here’s how to shrink the `TempDB` database:

CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (TEMPDEV, 1024)
GO
Copy Code

Always make sure to perform such operations during maintenance windows or low-usage periods to minimize the impact on the system’s performance.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

By following this guide, you can effectively manage the size of your `TempDB` and maintain optimal SQL Server performance.

In brief, our Support Experts demonstrated how to shrink TempDB in SQL Server with a Script.