sys.database_files and sys.sysfiles report on size, but that's just the current size of the file. Is there a way to find out what the initial size of the tempdb datafiles will be after it's recreated during a system restart?


Looks like it's

select name, file_id, size
from sys.master_files
where database_id = 2
and type = 0

(If necessary, the first datafile will be increased to the size of the model database).


A restart of SQL Server will reset the size of tempdb to its last configured size which is the last explicit size that was set by using a file sizing operation like ALTER DATABASE that uses the MODIFY FILE Option or DBCC SHRINKFILE command.

If you have not changed the tempdb size through any of the above commands, then it gets reset to MODEL database size.

Get current size of tempdb :

SELECT SUM(size)*1.0/128 AS [Tempdb size in MB]
FROM tempdb.sys.database_files

Below will explain the value of proper sizing of tempdb :

enter image description here

  • That explains the current size, but I wanted a way to find out the last configured size. So for example, if you're at a working state, my goal was to predict what the restart state will be. Turns out that value's available in sys.master_files. – Michael J Swart Sep 5 '13 at 19:52
  • @MichaelJSwart Interesting ... as the sys.master_files will have Current file size, in 8-KB pages. So if tempdb might have been grown using alter database modify or shrink using dbcc shrinkfile, wont it be inaccurate as you want the initial size ? Please correct me if I am wrong ! – Kin Shah Sep 5 '13 at 20:51
  • Hmm, In SQL 2008, that's what the docs say, but it's not what I see. The docs mention current size, but when tempdb files autogrow, sys.master_files(size) stays the same. Perhaps tempdb is a special case. It seems the docs say one thing, but the behaviour does something else. – Michael J Swart Sep 6 '13 at 12:26
  • @MichaelJSwart Found this connect (connect.microsoft.com/SQLServer/feedback/details/377223/…) saying The updating of sys.master_files occurs in an asynchronous manner from the actual grow/shrink of the file. There are race conditions where a crash might occur and the update not make it to sys.master_files. – Kin Shah Sep 6 '13 at 13:02
  • 1
    That's a great article. The discussion is interesting. My take is that Microsoft is comparing master_files to database_files after an ALTER DATABASE grow operation (First arrow in diagram). What I see is that master_files is not updated with autogrow (second arrow). But you don't have to take my word for it. Make you're tempdb autogrow and see that the size value in database_files changes but doesn't in master_files. – Michael J Swart Sep 6 '13 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.