TempDB file sizes

So, how large will Tempdb files be after reboot?

Normally when you look at the file sizes in SSMS it will show you the size the files are right now, but after a reboot tempdb files will reset to the configured file size in master database, use below sql to find out.

TempDB file size after reboot

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

TempDB 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

Fill TempDB (9GB), check on disk how the files are extended, check original and current size afterwards

CREATE TABLE #temptable (column1 char(3000) default 'a', column2 char(3000) default 'b')

SET NOCOUNT ON;
DECLARE @i INT = 1

BEGIN TRAN
WHILE @i <= 950000
BEGIN
INSERT INTO #temptable DEFAULT VALUES
SET @i += 1
END

COMMIT TRAN

DROP TABLE #temptable

Leave a Reply

Your email address will not be published. Required fields are marked *