The TempDB database is special in many ways, but an interesting aspect is that when its files automatically grow when they become full, this growth is not persisted and will be undone on the next restart of the SQL Server service. We can see how this works by looking at two system tables: sys.master_files and sys.database_files.
These tables hold details of the files which make up the database; sys.master_files lives in the master database and tracks all files for all databases, and sys.database_files sits in each database and holds details just for that one database. These tables have a very similar structure and are always in sync with each other for all databases, except for TempDB. The difference with TempDB is that when you manually grow a file the change is made in both tables, but an auto-growth only updates the local sys.database_files table, and not sys.master_files. So, on a restart when TempDB is recreated the information is used from sys.master_files, which did not know about the auto-growth, so it will revert back to the last manual size change.
Easiest way to check this is by comparing the sizes of the files in both tables:
-- ==============================
-- Check if TempDB has auto-grown
-- ==============================
SELECT
CASE m.type_desc WHEN 'ROWS' THEN 'DATA' ELSE 'LOG' END AS [Type],
d.file_id AS [File ID],
d.name AS [Logical name],
(m.size * 8) / 1024 AS [Starting size (MB)],
(d.size * 8) / 1024 AS [Current size (MB)],
CASE WHEN (d.size > m.size)
THEN '*** GROWN ***'
ELSE 'Not grown'
END AS [Status],
CASE WHEN (d.size > m.size)
THEN 'ALTER DATABASE TempDB MODIFY FILE (NAME = ''' + d.name + ''', SIZE = '
+ CAST((d.size * 8)/1024 AS varchar(10)) + 'MB);'
ELSE 'No need to modify'
END AS [Modify statement],
d.physical_name AS [Filename]
FROM sys.master_files AS m
JOIN tempdb.sys.database_files AS d ON m.file_id = d.file_id
WHERE DB_NAME(m.database_id) = 'tempdb'
ORDER BY [Type], [File ID];On an instance where TempDB has not auto-grown it will look like this:
And where it has auto-grown will look like:
If you restart in this state then TempDB will revert to the starting size, unless you manually run an ALTER DATABASE statement to grow it first.
What often happens in production systems is that TempDB will grow over time, especially if the server has been up for quite a while, and when it is restarted then TempDB will start back at the last manual size change, which could be significantly smaller than how it was before the restart. Then in normal operation following the restart, TempDB would again auto-grow in increments until the next restart.
Is this a problem? With fast storage, this will not cause much impact, but can still lead to the files becoming more fragmented, and having delays as it continues to auto-grow. The alternative is to check the state before a planned restart, and manually grow the files first (the above script contains the T-SQL to do that).
Best practice is still to pre-size TempDB on installation, but often you will not know realistically how large TempDB needs to be, so by starting with a sensible size and checking and then persisting any auto-growths, this will allow the server to naturally arrive at the correct configuration.