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

 

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

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
Lince Sebastian

Indian MSSQL DBA thriving in database management - ensuring efficiency and smooth operations. Devoted father of two and avid Clash of Clans player, driven by strategic thinking. Football fuels my passion - cheering Kerala Blasters and Arsenal. I share my professional and life insights through my blog.

*

إرسال تعليق (0)
أحدث أقدم