This post is all about the tempdb database. It is not about capacity planning, like size or number of data files – there is plenty of info about that out there. Instead, this is about managing it, in the sense of changing initial file size, adding a file, removing a file etc. SSMS isn’t obvious for some of these things for the tempdb database, and for some things SSMS is flat out wrong.
I tested all operations on SQL server 2017 and SSMS 17.8.1.
Tempdb is re-created at start-up. Or, rather re-initiated (in lack of better words). SQL Server do not delete the files and create new ones, that would take ages for large files if you don’t have Instant File Initialization (which you never have for log files). So how does SQL Server know what files to create, the logical and physical names, size etc? The answer is sys.master_files.
This is the “template” information for the tempdb files. I will refer to sys.master_files as the template below (not to be confused with model which is template for other things, but not the file aspects of tempdb). Here you see how the tempdb files will be created/initialized at next start-up. Don’t confuse this with tempdb.sys.database_files, which show the current state for the tempdb files. Below two queries will show you the current state for the tempdb files and also the template state:
--Current SELECT 'tempdb' AS db_name_ ,file_id ,name ,physical_name ,size * 8/1024 AS size_MB ,type_desc ,CASE WHEN is_percent_growth = 1 THEN CAST(growth AS varchar(3)) + ' %' ELSE CAST(growth * 8/1024 AS varchar(10)) + ' MB' END AS growth ,max_size * 8/1024 AS max_size_MB FROM tempdb.sys.database_files ORDER BY type, file_id --Template SELECT DB_NAME(database_id) AS db_name_ ,file_id ,name ,physical_name ,size * 8/1024 AS size_MB ,type_desc ,CASE WHEN is_percent_growth = 1 THEN CAST(growth AS varchar(3)) + ' %' ELSE CAST(growth * 8/1024 AS varchar(10)) + ' MB' END AS growth ,max_size * 8/1024 AS max_size_MB FROM master.sys.master_files WHERE DB_NAME(database_id) = 'tempdb' ORDER BY db_name_, type, file_id
Increasing current and template file size to larger than what it currently is
This is pretty straight-forward, both using T-SQL directly and also using SSMS. Here’s what it looks like in SSMS:
(The word “Initial” for the header “Initial Size (MB)” is pure nonsense. What you see is the current file size, picked up from sys.database_files. I am happy to see that the word “Initial” is removed in SSMS 18.0.)
To increase the file size you just type the desired, larger, size for the tempdb file. The T-SQL command to do this is also very straight forward, example:
ALTER DATABASE tempdb MODIFY FILE ( NAME = N'tempdev', SIZE = 5GB ) ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp2', SIZE = 5GB )
Note that SSMS will generate the file size in KB fpr the T-SQL command, but the T-SQL command accepts KB, MB, GB and even TB.
This operation increases the current file size as well as the template size.
Decreasing current file size
To reduce the current size, you can attempt a shrink operation, for instance using such as below T-SQL command:
USE tempdb DBCC SHRINKFILE(tempdev, 100)
The second parameter is the desired size in MB. This will only change the current size, not the template. In my experience, you should be prepared for a limited success to do shrink file a tempdb file. In many cases, you will have to re-start SQL Server so it will be created with the template size. Or, hunt down whatever is using it and take it from there.
Changing template file size to smaller than what it currently is
This is a bit trickier using the GUI. If you type a size which is smaller than current size in the GUI, then it will generate a DBCC SHRINKFILE command, as discussed above and that is not what we wanted to do here. So, what you can do is to specify a larger size than the current size, script the command to a T-SQL query window and in there just specify whatever size you want. It will generate a command such as:
ALTER DATABASE tempdb MODIFY FILE ( NAME = N'tempdev', SIZE = 100MB )
If the specified size is smaller than the current file size, then it will only change the template, not the current size.
This is dead-easy. In SSMS, just press the “Add” button and specify the desired name, size etc. This will generate T-SQL such as:
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdb3', FILENAME = N'R:\SqlData\a\tempdb_mssql_3.ndf' , SIZE = 5GB , FILEGROWTH = 200MB )
This will add it immediately and also to the template (both sys.database_files and sys.master_files).
This is simple enough if the file is empty. In SSMS you select the file and press the “Remove” button. It will generate T-SQL such as:
ALTER DATABASE tempdb REMOVE FILE tempdb3
But if the file is current used you will get an error message and nothing was changed: not the current state, nor the template.
You can always attempt to shrink first using the EMPTYFILE option:
USE tempdb DBCC SHRINKFILE(tempdb3, EMPTYFILE)
If you’re lucky, then the file was emptied and you can now use the ALTER command with the REMOVE FILE option. If not, then you are back to where you started.
You can try to re-start SQL Server and see if that allow you to remove the file. If not, then perhaps using the “failsafe” startup switch: /f (see Erin Stellato’s blog about this: https://www.sqlskills.com/blogs/erin/remove-files-from-tempdb/ ). Not be connected from SSMS when executing the commands might also help.
Changing other file attributes
If you want to change things like autogrow or maxsize, then just use SSMS or T-SQL directly. Here’s an example T-SQL command to change both autogrow and maxsize:
ALTER DATABASE tempdb MODIFY FILE ( NAME = N'tempdev', MAXSIZE = 500GB , FILEGROWTH = 500MB )
Moving a file
This operation requires a re-start. SSMS doesn’t allow you to do this, so use T-SQL directly, such as:
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\SqlData\b\tempdb.mdf')
Warning: Henrik reminded me in the comments section to add a warning here. “Make sure that R:\SqlData\b is present and that SQL Server has sufficient rights to create files here!” He is of course correct. In worst case you end up with SQL Server refusing to start if it cannot create tempdb where you have specified. In such case you can try the -f startup switch, perhaps a topic for another blog post.
Now, this is a sneaky one. It does change both sys.database_files and sys.master_files. So, looking only at the catalog views, you will be fooled that the file was moved immediately. But that didn’t happen, check the disk. Fortunately, the command comes with a message such as:
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
That makes it pretty obvious. Note that the old file will not be removed when you restart your SQL Server. Go delete that file, after verifying that the new file was indeed created (if the “old” one is still the current one, then you won’t be able to delete it as long as SQL Server is running).
Managing tempdb isn’t that hard. As soon as you understand that we have the current state (tempdb.sys.master_files) and the template (sys.master_files), it is easier to understand. And, yes, SSMS is a bit quirky for these operations. The reason, methinks, is that it uses the same GUI as for other databases, which doesn’t have the aspect of a “template”.
Did I miss any operation? Let me know and I’ll might add it.