/***************************************************************
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
GO