TempDB Actual Current Size

TempDB 1 Minute

Sometimes, the actual physical size of TempDB is not always shown correctly when you run the below query.

Select Database_id,((size*8)/1024) as SIZE_IN_MB, Name,Type_desc,Physical_Name from sys.master_files where database_id=2

Or when you right click on the TempDB database and look in the properties.

It only shows the last configured\start up size of the TempDB. This happens when the TempDB grows beyond the initial configured size.So, to get the actual size of the TempDB, we need to run

sp_helpdb 'TempDB'

You can also use below query to find the actual current size of the TempDB.
select Name,((size*8)/1024) as SIZE_IN_MB from Tempdb.sys.database_files

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published