How to find tempdb file size in SQL server and Azure SQL Managed Instance (configured / initial / Current)

 

In SQL Server, TempDB is a system database used to store temporary objects such as temporary tables, table variables, and other internal objects created during query processing. It is essential for managing sorting, grouping, and joins, as well as for other temporary storage needs in SQL Server.

We can determine the current size of the TempDB database by querying the tempdb.sys.database_files dynamic management view (DMV). To find the configured or initial size of the tempdb database, you can query the sys.master_files DMV.

Please use the following query to determine the configured or initial size of the TempDB files.

Copied!

 USE master;
GO
-- Check the Initial size of TempDB
SELECT
    name AS [Database Name],
    type_desc,
    size * 8 / 1024 AS [Initial Size (MB) ],
    growth * 8 / 1024 AS [Autogrowth Setting (MB) ]
FROM
    sys.master_files
WHERE 
  database_id = DB_ID('tempdb');

Please use the below query to retrieve the current size of the TempDB files

Copied!

Select
    name,
    file_id,
    size * 8 / 1024 AS [Current Size (MB) ],
    growth * 8 / 1024 AS [Autogrowth Setting (MB) ]
from
    tempdb.sys.database_files