Blog

There is always a limit on how large tempdb can get. Even if there are no size limitations on the data files in tempdb, the limit is still the free space on disk drives. Here I’ll show how to calculate the maximum size of tempdb (data only, not log).

Tempdb files

The current settings on data files in tempdb (or any other database for that matter) can be found in the system table sys.database_files. The column size shows how large the file currently is. If the column growth is > 0 it means the file can grow from its current size. The column max_size shows if there is a fixed max size set for the file. If it is -1, the file will grow until the disk is full.

The same information can be seen in SSMS by right-clicking tempdb and selecting “Properties” and the page “File”:

Calculating the max size of tempdb

The tempdb max size is a summary of:

* Current size of files that has autogrowth disabled.
* Max size (that will fit on disk) of files that has a fixed max size and autogrowth enabled.
* Files without max size that will fill the disk and autogrowth enabled.

The following T-SQL will create a procedure that calculates the tempdb max size:

/***************************************************************
Tomas Lind 2014
Tempdb max size


Usage:
DECLARE @TempdbMaxSize INT
EXEC dbo.GetTempdbMaxSize @TempdbMaxSize OUTPUT
SELECT @TempdbMaxSize

Note that the calculation only includes data files (not log)
so the total size of tempdb calculated is excluding the size of log files.

 All calulations are done via the table variable @TBL_TEMPDB_FILES,
 so if you want to check how the total is calculated, just add a
 “SELECT * FROM @TBL_TEMPDB_FILES” in the end.
***************************************************************/

CREATE PROCEDURE dbo.GetTempdbMaxSize
	(
		@TempdbMaxSize INT OUTPUT
	)

AS

SET NOCOUNT ON

DECLARE
	@TotalRowIds INT
	,@RowCounter INT = 1
	,@ActualMaxSize INT
	,@DriveFreeSpaceMB INT

DECLARE @TBL_TEMPDB_FILES TABLE
	(
		RowId INT IDENTITY(1,1) NOT NULL
		,LogicalFileName SYSNAME NOT NULL
		,CanGrow BIT NOT NULL
		,CurrentSizeMB INT NOT NULL
		,MaxSizeMB INT NOT NULL
		,DriveLetter CHAR(1) NOT NULL
		,FileNoInDrive INT NOT NULL
		,DriveFreeSpaceMB INT NOT NULL
		,ActualMaxSize INT NOT NULL
	)

INSERT @TBL_TEMPDB_FILES
	(
		LogicalFileName
		,CanGrow
		,CurrentSizeMB
		,MaxSizeMB
		,DriveLetter
		,FileNoInDrive
		,DriveFreeSpaceMB
		,ActualMaxSize
	)
SELECT
	name
	,CASE WHEN growth > 0 THEN 1 ELSE 0 END
	,size*8/1024
	,CASE WHEN max_size = -1 THEN -1 ELSE max_size*8/1024 END
	,SUBSTRING(physical_name, 1, 1)
	,ROW_NUMBER() OVER (PARTITION BY SUBSTRING(physical_name, 1, 1) ORDER BY name)
	,available_bytes/1024/1024
	,0
FROM
	tempdb.sys.database_files T0
CROSS APPLY
	sys.dm_os_volume_stats(DB_ID('tempdb'), T0.file_id)
WHERE
	type_desc = 'ROWS'

SELECT @TotalRowIds = COUNT(*) FROM @TBL_TEMPDB_FILES

UPDATE @TBL_TEMPDB_FILES SET DriveFreeSpaceMB = 0 WHERE FileNoInDrive > 1

WHILE @RowCounter <= @TotalRowIds BEGIN

	IF EXISTS (SELECT * FROM @TBL_TEMPDB_FILES WHERE RowId = @RowCounter AND FileNoInDrive > 1)  BEGIN
		UPDATE @TBL_TEMPDB_FILES SET DriveFreeSpaceMB = CASE WHEN @DriveFreeSpaceMB > 0 THEN @DriveFreeSpaceMB ELSE 0 END WHERE RowId = @RowCounter
	END
	ELSE
		SELECT @DriveFreeSpaceMB = DriveFreeSpaceMB FROM @TBL_TEMPDB_FILES WHERE RowId = @RowCounter

	SELECT
		@ActualMaxSize =
			CASE
				WHEN @DriveFreeSpaceMB > 0 THEN
					CASE WHEN CanGrow = 0 THEN CurrentSizeMB
					WHEN CanGrow = 1 AND MaxSizeMB = -1 THEN DriveFreeSpaceMB + CurrentSizeMB
					WHEN  CanGrow = 1 AND MaxSizeMB > 0 THEN MaxSizeMB
					END
				ELSE
					CurrentSizeMB
			END
	FROM
		@TBL_TEMPDB_FILES T0
	WHERE
		RowId = @RowCounter

	SELECT @DriveFreeSpaceMB = DriveFreeSpaceMB - @ActualMaxSize FROM @TBL_TEMPDB_FILES WHERE RowId = @RowCounter
	
	UPDATE @TBL_TEMPDB_FILES SET ActualMaxSize = @ActualMaxSize WHERE RowId = @RowCounter

	SELECT @RowCounter = @RowCounter + 1
END

SELECT @TempdbMaxSize = SUM(ActualMaxSize) FROM @TBL_TEMPDB_FILES

Note that the calculation only includes data files (not log) so the total size of tempdb calculated is excluding the size of log files.

All calculations are done via the table variable @TBL_TEMPDB_FILES, so if you want to check how the total is calculated, just add a “SELECT * FROM @TBL_TEMPDB_FILES” in the end.