Ways to shrink tempdb (reproduced)

Sometimes running a query with a large amount of data on the database can result in a spike in tempdb data, see the following linked article:

SQL Server One query statement causes tempdb to grow by 55G

After finding the issue of tempdb spikes, the next question is how to shrink tempdb. The following article describes how to shrink tempdb in detail:

The simplest approach: Restart the SQL Server service
If SQL Server is installed in the default path, then the tempdb.mdf and templog.ldf files are in the following path:
"C:\Program Files\Microsoft SQL Server\mssql\data\"
If the partition on your hard disk does not have enough space to meet the growth requirements for that temporary file, use the following command to move to another partition:
--Moving Master data files
ALTER DATABASE tempdb MODIFY FILE
(Name= ' tempdev ',
FILENAME = ' d:\tempdb.mdf ')
--Move the main log file
ALTER DATABASE tempdb MODIFY FILE
(Name= ' Templog ',
FILENAME = ' d:\templog.ldf ')
How to shrink the tempdb database-the following is a complete analysis from Microsoft Official:
This article discusses the three ways to shrink the tempdb database to less than its last configured size. The first method gives you full control over the size of the tempdb file, but it requires you to restart SQL Server. The second method shrinks tempdb as a whole, but it has some limitations that may include restarting SQL Server. The third method allows you to shrink a single file in tempdb. The last two methods require no activity to occur in the tempdb database during a shrink operation.
Note: If you are using SQL Server 2005, these methods still apply. However, you should use SQL Server Management Studio instead of Enterprise Manager and Query Analyzer to perform these operations.
TEMPDB Information
Tempdb is a temporary work area. SQL Server, among other things, uses tempdb for:
• Storage of temporary tables created explicitly.
• Save the worksheet for intermediate results created during query processing and sorting.
• materialized static cursors.
The information that SQL Server logs in the tempdb transaction log is sufficient for rolling back the transaction, not enough to re-execute the transaction during database recovery. This feature improves the performance of the INSERT statement in tempdb. Also, because tempdb is recreated each time you restart SQL Server, you do not need to log information that is used to re-execute any transactions. Therefore, there are no transactions to roll forward or roll back. When SQL Server starts, re-creates tempdb by using a copy of the model database and resets it to the last configured size.
By default, the tempdb database is configured to grow automatically as needed, so this database may eventually grow to greater than the desired size. Simply restarting SQL Server resets the size of tempdb to the size of the last configuration. The configured size is the last explicit size set with the file size change operation, such as the ALTER DATABASE or DBCC shrinkfile statement with the MODIFY file option. This article describes three methods that you can use to shrink tempdb to a size smaller than its configuration.

Method 1 for shrinking Tempdb (this method is effective after I try!) )
This method requires that you restart SQL Server.
1. Stop SQL Server. Open a command prompt, and then type the following command to start SQL Server:
Sqlservr-c-F
The-C and-F parameters enable SQL Server to start in a minimal configuration mode with a tempdb size of 1 MB for the data file and a log file of tempdb of 0.5 MB.
Note: If you are using a SQL Server named instance, you must switch to the appropriate folder (Program Files\Microsoft SQL Server\mssql$instance Name\binn) and use the-s switch (-s%instance_ name%).
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL command:
ALTER DATABASE tempdb MODIFY FILE
(NAME = ' tempdev ', SIZE = TARGET_SIZE_IN_MB)
--desired target size for the data file
ALTER DATABASE tempdb MODIFY FILE
(NAME = ' Templog ', SIZE = TARGET_SIZE_IN_MB)
--desired target size for the log file
3. Stop SQL Server by pressing CTRL-C in the command Prompt window, restart SQL Server as a service, and then verify the size of the tempdb.mdf and templog.ldf files.
The limitation of this method is that it can only operate on the default tempdb logical file Tempdev and Templog. If you add additional files to tempdb, you can shrink them after you restart SQL Server as a service. All tempdb files are recreated during startup, so they are empty and can be deleted. To delete other files in tempdb, use the ALTER DATABASE command with the Remove File option.

method of shrinking Tempdb 2
Use the DBCC shrinkdatabase command to shrink the tempdb database as a whole. DBCC Shrinkdatabase receives the parameter target_percent, which is the desired percentage of the remaining free space in the database file after the database shrinks. If you use DBCC shrinkdatabase, you may have to restart SQL Server.
Important: If you run DBCC shrinkdatabase, the tempdb database cannot be in the event of another activity. To ensure that other processes cannot use tempdb when you run DBCC shrinkdatabase, you must start SQL Server in single-user mode. For more information, refer to the section on performing the results of DBCC shrinkdatabase or dbccshrinkfile when using Tempdb in this article.
1. Determine the space currently in use in tempdb by using the sp_spaceused stored procedure. Then, calculate the percentage of the remaining free space, which will be used as a parameter to the DBCC Shrinkdatabase, which is based on the desired database size.
Note: In some cases, you may have to perform sp_spaceused @updateusage =true to recalculate the space used and to obtain updated reports. For more information about sp_spaceused stored procedures, see SQL Server Books Online.
Consider the following example:
Assume
Tempdb
There are two files: the primary data file (tempdb.mdf) and the log file (Tempdb.ldf), each of which is a size of two megabytes and a MB. Assume
sp_spaceused
The report master data file contains up to MB of data. It is also assumed that you want to shrink the master data file to a size of MB. Calculates the desired percentage of the remaining free space after shrinking, which is MB-60 MB = MB. Now, divide by the MB = 25%, which is your target_percent. The transaction log files are shrunk accordingly, leaving 25% MB of free space after the database shrinks.
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL command:
DBCC SHRINKDATABASE (tempdb, ' target percent ')
--This command shrinks the tempdb database as a whole
Using the DBCC shrinkdatabase command with the tempdb database has limitations. The target size of the data file and log file cannot be less than the size specified when the database was created or the last size explicitly set with the file size change operation (such as the ALTER DATABASE command with the MODIFY file option or the DBCC shrinkfile command). Another limitation of DBCC Shrinkdatabase is the calculation of the Target_percentage parameter and its dependence on the space currently in use.

method of shrinking Tempdb 3
Use the command DBCC shrinkfile to shrink a single tempdb file. DBCC SHRINKFILE provides more flexibility than DBCC SHRINKDATABASE because you can use it on a single database file without affecting other files that belong to the same database. The DBCC Shrinkfile receives the target size parameter, which is the final sized of the desired database file.
Important: The DBCC shrinkfile command must be run when no activity occurs in the tempdb database. To ensure that other processes cannot use tempdb while executing DBCC shrinkfile, you must restart SQL Server in single-user mode. For more information about the DBCC SHRINKFILE, see the section stated performing DBCC SHRINKDATABASE or dbccshrinkfile when using Tempdb.
1. Determine the desired size of the master data file (tempdb.mdf), log file (templog.ldf), and/or other files added to tempdb. Make sure that the space used in these files is less than or equal to the desired target size.
2. Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands for a specific database that needs to be shrunk:
Use tempdb
Go
DBCC SHRINKFILE (tempdev, ' target size in MB ')
Go
--This command shrinks the primary data file
DBCC SHRINKFILE (templog, ' target size in MB ')
Go
--This command shrinks the log file and look at the last paragraph.
One advantage of DBCC shrinkfile is that it can reduce the file size to less than its original size. You can perform DBCC shrinkfile on any data file or log file. One limitation of DBCC shrinkfile is that you cannot make the database smaller than the size of the model database.
In SQL Server 7.0, transaction log shrinkage is a deferred operation, and you must perform log truncation and backups to help shrink operations in the database. However, by default, tempdb sets the trunc log on chkpt option to ON, so that you do not have to perform log truncation for the database. For additional information about how to shrink the database transaction log in SQL Server 7.0, click the following article number to view the article in the Microsoft Knowledge Base:
256650
(http://support.microsoft.com/kb/256650/)
INF: How to shrink the SQL Server 7.0 transaction log
Perform DBCC shrinkdatabase or dbccshrinkfile results when using Tempdb
When you are using tempdb, if you try to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE command, you may receive multiple consistency errors that are similar to the following types, and the shrink operation may fail:
Server:msg 2501, Level A, State 1, line 1 Could not find table named ' 1525580473 '. Check sysobjects. Or
Server:msg 8909, Level A, State 1, line 0 Table corrupt:object ID 1, index ID 0, page ID%s_pgid. The PageId in the page header =%s_pgid. Although error 2501 may not represent any corruption in tempdb, it can cause the shrink operation to fail. Unlike this, error 8909 may indicate corruption in the tempdb database. You should restart SQL Server to recreate tempdb and clear conformance errors. However, keep in mind that physical data corruption like error 8909 may have other causes, including input/output subsystem issues.

Ways to shrink tempdb (reproduced)