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

Two days ago I wrote article about SQL SERVER – TempDB Restrictions – Temp Database Restrictions. Since then I have received few emails asking details about TempDB. I use following T-SQL Script to know details about my TempDB. I have found this script from MSDN site. I have mentioned original source link in reference at the end of this article.
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.'

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL

6 thoughts on “SQL SERVER – T-SQL Script to Find Details About TempDB

  1. 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!

    Liked by 1 person

  2. 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!


  3. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s