SQL Server tempdb database size - file creation and file growth

SQL Server tempdb is a system database that used as global resource for temporary user objects, internal objects, row versioning and other operations. The database grows as these resources demand increases, and below the maximum file size limit if previously configured.

As tempdb database is the only shared database of the entire SQL instance used for these operations, it is crucial to understand tempdb configuration, resource demand and maintenance. We will be discussing about its size in this post.

SQL Server (from SQL Server 2005) has some catalog views to return information about database files size. There are sys.master_files and sys.database_files. sys.master_files is a system-wide view while the sys.database_files is a per-database view. Most of the times, these two views return the same result in terms of database files size, however not on tempdb database.

Some of you may already know that the sys.database_files returns the actual tempdb database files size. However, the tempdb database files size returned from sys.master_files has some important information.

Let's find out.

The query below returns the information of database files size for model, tempdb and other database from both sys.database_files and sys.master_files views.

USE model;
SELECT d.name, d.size / 128.0 AS size_MB
FROM sys.database_files d

USE tempdb;
SELECT d.name, d.size / 128.0 AS size_MB
FROM sys.database_files d

USE [master];
SELECT m.name, m.size / 128.0 AS size_MB
FROM sys.master_files m

Both views returns the same result for model and tempdb database size: model data file modeldev is around 3MB, and tempdb data file tempdev is 8MB.

Now, use alter database statement to change the tempdb data file size to 16MB.

ALTER DATABASE tempdb
MODIFY FILE
(
 NAME = tempdev,
 SIZE = 16MB
);

USE tempdb;
SELECT d.name, d.size / 128.0 AS size_MB
FROM sys.database_files d;

USE [master];
SELECT m.name, m.size / 128.0 AS size_MB
FROM sys.master_files m;

After the changes, both catalog views return same result that tempdev size is now 16MB.

Now, let's perform some task to require large tempdb data file to accommodate the resource demand. This task will force tempdb to grow. The current setting of filegrowth is 10% and unlimited size.

CREATE TABLE #tbl
(
 col1 nchar(4000) DEFAULT ''
);

DECLARE @i int = 0
WHILE @i < 3000
BEGIN
 INSERT INTO #tbl
 VALUES (DEFAULT);

 SET @i = @i + 1;
END;

DROP TABLE #tbl;

USE tempdb;
SELECT d.name, d.size / 128.0 AS size_MB
FROM sys.database_files d;

USE [master];
SELECT m.name, m.size / 128.0 AS size_MB
FROM sys.master_files m;

The user temporary table creation forces the tempdb data file to grow to around 25.9MB and log file to 1.25MB as reflected from sys.database_files. However, the sys.master_files result still show tempdev as 16MB and templog as 0.5MB. This is the time where sys.master_files start to return incorrect file size for tempdb database.

So how important is this 'incorrect' file size returned from sys.master_files.

SQL Server recreate tempdb database files each time the SQL Server instance is restarted, regardless if the tempdb physical files exist at the location.

Stop the SQL Server service from SQL Server Configuration Manager, and start the SQL Server. Run the following query.

USE tempdb;
SELECT d.name, d.size / 128.0 AS size_MB
FROM sys.database_files d;

USE [master];
SELECT m.name, m.size / 128.0 AS size_MB
FROM sys.master_files m;

Both catalog views show both tempdb data and log files are returned to the size (16MB and 0.5MB respectively) that was set during the alter database statement.

Observation
After SQL Server restart, tempdb database size is not using model database size (data file - 3.06MB and log file - 0.75MB) as its default size. It does not keep its size that was caused by the file growth. Instead, it uses the original size or the size that was set using alter database statement, that could be obtained from sys.master_file catalog view.

What does that mean for you
- If you have observed that the current tempdb size is what required for regular operation and it is caused by previous file growth (you can now tell from the difference between the two catalog views), you may want to use alter database to set the tempdb to that size so if SQL Server restart, it doesn't have to wait for file growth operation when demand kick in.

- If you are extracting information of tempdb files size for baselining, monitoring, and whatever reason, use sys.database_files to obtain its correct file size.

- If you have created and configured multiple tempdb data files to avoid tempdb PAGELATCH contention, restarting the SQL Server will recreate the tempdb files as the same configuration shown in sys.master_files. So, if you have set all the tempdb data files appropriately as shown in this view, you don't have to worry of reconfiguring them after restart.

- If you restore master database during disaster recovery process or move the system database to other location, after SQL Server restart, the tempdb files creation will configured the same way as stored in the new master database.