Recently I had to deal with re-sizing multiple TempDB files on a production server. My task was to reduce the size of the mdf, ldf and 15 ndf files because they were created with the wrong size initially.

As many of the DBAs already know, altering this system database is quite different compared to user databases and even compared to other system databases. Although it is relatively simple to add files, modify the file path, rename the file or increase the file size, it is actually somewhat challenging when it comes to reducing the size of existing TempDB files.

After many trials and tests I came away with 2 viable methods to accomplish this task but each option has very important benefits and drawbacks to consider.

METHOD 1: Shrink & Modify file

First of all, Microsoft strongly discourages shrinking any TempDB files that are actively being used. That's because "you may receive multiple consistency errors" that could not only disrupt user activity ("Worktables that hold intermediate results created during query processing and sorting.") happening inside TempDB at the time but may also abort the shrink operation altogether. However, if user activity is minimal or none in your case you can follow these 2 steps to reduce the TempDB file sizes:

1. Run DBCC SHRINKFILE command on each file you want to reduce the size for.

USE TempDB
GO
DBCC SHRINKFILE (N'logical_file_name', 5) -- size in MB

2. Then, run ALTER DATABASE statement for each of these files with the size you'd like them to be. This will cause the new size to register in master.sys.master_files, which is the system catalog that SQL Server uses to recreate a fresh, blank TempDB any time the server/instance is restarted.

USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME=' logical_file_name, SIZE=6MB)

Note that until SQL Server is restarted (when TempDB is created fresh) the above changes will not show the new values in the Database Properties or the Shrink File GUI. However, you can verify the changes immediately by running:

SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, *8/1024 SIZE_MB
FROM MASTER.SYS.MASTER_FILES
WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'

More suggestions on how to shrink TempDB files.

METHOD 2 - Modify files in Restricted user mode

This method eliminates the risk of any consistency errors while it assures successful operation to reduce file sizes. But on the flip side, you will have to disconnect all users before you can begin your work. However, if you plan it well, you can probably accomplish these changes in about 10 minutes by following the steps below:

1. Establish a Dedicated Administrator Connection (DAC) from Management Studio to the instance you are working on. You can do this by simply prefixing the tag "Admin:" in front of the instance name. E.g., ADMIN:ProdServer\Instance1
2. Run ALTER DATABASE with the REMOVE option to mark the ndf files obsolete.
   USE MASTER
   GO
   ALTER DATABASE TEMPDB REMOVE FILE logical_file_name
3. RDP to the server and...
   1. Stop the instance from command prompt. For default instance:

      C:\>NET STOP MSSQLSERVER

      or

      NET STOP "SQL Server (MSSQLSERVER)"

      For named instance:
      C:\> NET STOP "SQL Server ( instancename )" or NET STOP MSSQL$instancename

   2. Start the instance in restricted mode (change the path to where you SQL bits are)
      C:\SQL\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -f
4. Alter TempDB with the new initial file size for the mdf and ldf files only from the dedicated connection in SSMS (until you restart SQL Server in normal mode, this is the only connection available)

   USE MASTER
   GO
   ALTER DATABASE TEMPDB MODIFY FILE (NAME='logical_file_name', SIZE=6MB)
   

5. Go back to the command prompt window and Hit Ctrl + C to get out of the restricted mode (say "Yes" when the prompt asks if you want to stop the SQL Server). Then start the instance in normal mode. For default instance:

   C:\>NET START MSSQLSERVER
   

   or

   NET START "SQL Server (MSSQLSERVER)"
   

   For named instance:

   C:\> NET START "SQL Server ( instancename )"
   or
   NET START MSSQL$instancename
   

6. Alter TempDB with Add File option with the new size specification for the ndf files.

   ALTER DATABASE TEMPDB ADD FILE (NAME=logical_file_name, FILENAME='C:\SQL\Data\logical_file_name.ndf',SIZE=6MB)
   

   At the end run this query again to ensure all your changes are properly showing in the Master database:

   SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, *8/1024 SIZE_MB
   FROM MASTER.SYS.MASTER_FILES
   WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'
   

Conclusion

Clearly the first method is a lot simpler and faster but you'd be taking a big risk of possibly corrupting data in TempDB. Plus, the shrink operation may not even finish due to concurrent usage of those files. The second method, while being more elaborate, ensures a smooth operation. However it does require downtime.

Your constructive comments are always welcome!