Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

Tempdb size resets after a SQL Server service restart


By:   |   Read Comments (6)   |   Related Tips: 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.

tempdb

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

tempdev

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.

files

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

tempdev

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.

tempdev

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.

tempdev

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Simon Liew 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, December 21, 2017 - 4:10:38 PM - Peter Phipps Back To Top

Nice article but I am certianly not seeing the the same results. I am using SQL 2016 Std on Win 2016 Std (SQL install was SP1 ; l now SP1 CU6). I installed 4 instances. During the install for the tempdb data files (4 files) I set an initial size to 512MB and autogrowth of 512MB for three instances and the fourth instance with 256MB and 256MB. These settings are reflected in sys.master_files of each instance. However, upon restart of SQL the size of tempdev is 1024MB with the other three files reflecting their initial settings. Do have an explanation for this? Did tempdev autogrow during the restart? I don't see evidence of autogrowth.

... And now for the answer ... I think ...

Post installation I set the initial size to 1024MB of the one data file for the MODEL database. Confirmation of the effect on tempdb was confirmed by changing the initial size of the model database file to various settings and confirming the temdev file in tempdb reflected this. So now the question does SQL Server behave like this?


Friday, June 02, 2017 - 3:30:26 AM - Arthur Kavanaugh Back To Top

Hi Simon, Thank you very much for this post and the scripts. This is very good to keep in mind.

I had some trouble with tempdb last month. At last I had found the problem. Perhaps it is something for you to research about?

The problem I discovered was a huge templog. The templog seems full but was, after some research, 95% empty. Although empty, the big templog caused a lot of problems with other databases on our system. The I/O raised a lot and some applications got time-outs.

Finally, I even found out why templog grows so much and tackled the problem. 

Kind Regards,

Arthur Kavanaugh

 


Wednesday, May 03, 2017 - 6:58:56 PM - George Xiao Back To Top

 Thanks for this nice article Simon. It helped me a lot


Friday, April 28, 2017 - 7:55:28 AM - Simon Liew Back To Top

Thank you Greg.

 

Alain,

I'm glad this tip helped you.


Wednesday, April 26, 2017 - 9:59:23 AM - Greg Robidoux Back To Top

Hi Simon,

Congrats on your 25th tip and becoming a MSSQLTips Trendsetter.

-Greg


Wednesday, April 26, 2017 - 7:24:00 AM - Alain Martin Back To Top

 Ha thanks for this clear explanation! Was a bit confuse about this :-)

 


Learn more about SQL Server tools