By: Simon Liew | Last Updated: 2017-04-26 | Comments (6) | System Databases
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?
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.
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.
Last Updated: 2017-04-26
About the author
View all my tips