Tempdb size resets after a SQL Server service restart

By: Simon Liew   |   Updated: 2017-04-26   |   Comments (8)   |   Related: More > System Databases

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

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips