SQL SERVER – T-SQL Script to Find Details About TempDB Information

Two days ago I wrote an article about SQL SERVER – TempDB Restrictions – Temp Database Restrictions. Since then I have received few emails asking details about Temp DB. I use following T-SQL Script to know details about my TempDB. This script is a pretty old script but it does work great most of the time. I strongly encourage all of you to use a script to check your TempDB Information.

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 AutogrowthStatus,
growth AS 'GrowthValue',
'GrowthIncrement' =
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.'
FROM tempdb.sys.database_files;

Above script will return following result set.

FileName FileSizeinMB AutogrowthStatus GrowthValue GrowthIncrement
tempdev 8 Autogrowth is on. 10 Growth value is a percentage.
templog 0.5 Autogrowth is on. 10 Growth value is a percentage.

Here are some additional blog posts related to Temp DB which you may find useful.

What is the Initial Size of TempDB? – Interview Question of the Week #120

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Solution – Log File Very Large – Log Full
Next Post
SQL SERVER – Get Information of Index of Tables and Indexed Columns

Related Posts

6 Comments. Leave new

  • I am in search of a script that will allow me to manage the autogrowth feature by percent because there are 400+ DBs that need to be updated. Is there a built-in system funciton that I could use to accomplish this? I have found tons of scripts that pertain to managing a database’s growth by megabyte but none by percent. Your expertise would be very much appreciated in this matter!

  • I have a question regarding the above script. When I run it in SQL Server Management Studio, I get ‘Msg 208, Level 16, State 1, Line 1 Invalid object name ‘tempdb.sys.database_files’. Why is this? I realize it is probably something relatively simple, but I am relatively new to this side of sql server and I have been researching this for a while now with no success. It seems like it should be in line with DBName.Role.Table, but again I cannot locate this in sql either. Any help you could give me would be much appreciated. I am working with SQL Server Management Studio 2008 R2. Thanks!

  • is it UseFul in Optimize Data base When TempDB Size is Less?

    How to decrease TempDB Size?

  • May be path invalid

  • Msg 4145, Level 15, State 1, Line 40
    An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.

  • The script above has some HTML code that covered SQL code.
    Here is what the script is supposed to be.

    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 grows to a maximum size of 2 TB.’
    ,growth AS ‘GrowthValue’
    ,’GrowthIncrement’ = CASE
    WHEN growth = 0
    THEN ‘Size is fixed.’
    WHEN growth > 0
    AND is_percent_growth = 0
    THEN ‘Growth value is in 8-KB pages.’
    ELSE ‘Growth value is a percentage.’
    FROM tempdb.sys.database_files;


Leave a Reply Cancel reply

Exit mobile version