Problem
I checked the size of the SQL Server tempdb database and it was 10GB. After restarting the SQL Server service, the tempdb database size went down to 1GB. Doesn’t tempdb maintain its database and transaction log size after a SQL Server restart?
Solution
The behavior described in the problem statement is SQL Server’s behavior by design. To elaborate, the tempdb size will reset itself to the last manually configured size when the SQL Server service is restarted. The tempdb database will increase in size due to auto-growth, but this last size is not retained after a SQL Server service restart.
Internally within SQL Server, the current tempdb database size and the last manually configured database size are stored in different system catalogs.
- The current tempdb database size can be found by querying DMV tempdb.sys.database_files.
- The last manually configured tempdb database size can be found by querying DMV sys.master_files.
The steps below simulate the scenario outlined in the problem statement on SQL Server 2016 SP1 Developer Edition.
Step 1 – Manually set tempdb database size
My initial tempdb configuration is two data files with 8 MB each and the transaction log file is 8 MB. This tempdb configuration is the result after an installation of SQL Server 2016. This configuration is treated as the last configured size unless the tempdb size is manually altered from SSMS or a T-SQL command.
The T-SQL command below will change the two tempdb database data files to 500MB each and the transaction log to 1GB.
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 512000KB , FILEGROWTH = 1GB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 512000KB , FILEGROWTH = 1GB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 1048576KB , FILEGROWTH = 1GB )
GO
Step 2 – Check tempdb database size
Whether checking the database size from SSMS or querying the DMVs, it will report the same configured tempdb size.
Querying DMV sys.database_files and sys.master_files will output the same result.
-- configured size
SELECT
name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'tempdb'
ORDER BY type_desc DESC, file_id
GO
-- current size
SELECT
name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]
FROM tempdb.sys.database_files
ORDER BY type_desc DESC, file_id
GO
Step 3 – Fill up tempdb
The code below will consume about 9GB of space in the tempdb database which causes the tempdb data files to auto-grow.
CREATE TABLE #LargeTempTable (col1 char(3000) default 'a', col2 char(3000) default 'b')
SET NOCOUNT ON;
DECLARE @i INT = 1
BEGIN TRAN
WHILE @i <= 950000
BEGIN
INSERT INTO #LargeTempTable DEFAULT VALUES
SET @i += 1
END
COMMIT TRAN
DROP TABLE #LargeTempTable
Step 4 – Check tempdb size again
It is easy to use SSMS to check the current tempdb size. If you right click on tempdb and select Properties the following screen will open. The tempdb database properties page will show the current tempdb size as 4.6 GB for each of the two data files and 2 GB for the log file.
If you query DMV sys.databases_files, this also reports the current database size.
-- current size
SELECT
name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]
FROM tempdb.sys.database_files
ORDER BY type_desc DESC, file_id
To get the last manually configured tempdb database size, you need to query DMV sys.master_files. When the SQL Server service is restarted, the tempdb files will reset to these configured sizes.
Here is the query to get the sizes that will be used if tempdb is recreated.
-- configured size
SELECT
name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'tempdb'
ORDER BY type_desc DESC, file_id
GO
This matches what we show in Step 2.
Step 5 – Restart SQL Server service
After the SQL Server service is restarted, you will see the tempdb size will be reset to the last manually configured size specified in DMV sys.master_files.
Summary
The tempdb database data files and transaction log size can be modified using SSMS or T-SQL commands. Whether the file sizes grow or are shrunk, the last manually configured size will be reflected in DMV sys.master_files.
It is essential to understand the difference between DMV reporting the current tempdb database size versus the last manually configured size. By comparing sys.master_files and sys.database_files, you can also easily tell how much tempdb has auto-grown since the last time SQL Server was started.
Next Steps
- Learn about the tempdb database
- Check out these other Database Administration Tips
Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.
- MSSQLTips Awards: Trendsetter (25+tips) – 2017 | Author of the Year Contender – 2016, 2017
