Temp DB size and Growth parameters

You can view the size and file growth parameters of the tempdb data or log files by using following script :

  

 

SELECT

    name AS FileName,

    size*1.0/128 AS FileSizeinMB,

    CASE max_size

        WHEN 0 THEN 'Autogrowth is off.'

        WHEN -1 THEN 'Autogrowth is on.'

        ELSE 'Log file will grow to a maximum size of 2 TB.'

    END,

    growth AS 'GrowthValue',

    'GrowthIncrement' =

        CASE

            WHEN growth = 0 THEN 'Size is fixed and will not grow.'

            WHEN growth > 0 AND is_percent_growth = 0

                THEN 'Growth value is in 8-KB pages.'

            ELSE 'Growth value is a percentage.'

        END

FROM tempdb.sys.database_files;

GO

I hope this will helps you all