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

December 17, 2007

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.

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

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 AutogrowthStatus,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth &amp;amp;gt; 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

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)

SQL Scripts, SQL Server, SQL TempDB

SQL SERVER – Solution – Log File Very Large – Log Full

SQL SERVER – Get Information of Index of Tables and Indexed Columns

6 Comments. Leave new

Marilyn
November 18, 2008 2:15 am
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!
Reply
Tgra3810
October 19, 2010 3:29 am
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!
Reply
Nirav
December 23, 2010 11:54 am
is it UseFul in Optimize Data base When TempDB Size is Less?
How to decrease TempDB Size?
Reply
gopi
July 24, 2015 8:52 pm
May be path invalid
Reply
alala
April 2, 2019 2:17 am
Msg 4145, Level 15, State 1, Line 40
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.
Reply
Dan
May 10, 2019 12:38 am
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.’
END
,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.’
END
FROM tempdb.sys.database_files;
GO
Reply