TempDB Information starts with the files and their actual locations. I inspect every data and log file before proposing changes.
After my restrictions article, readers asked for details about their TempDB. The original script used tempdb.sys.database_files, but its CASE expression described every positive maximum size as a 2TB log limit. That is not what every row means.
SELECT name AS file_name, type_desc,
size * 8.0 / 1024 AS size_mb,
CASE WHEN growth = 0 OR max_size = 0 THEN 'Disabled' ELSE 'Enabled' END AS autogrowth,
CASE WHEN max_size = -1 THEN NULL ELSE max_size * 8.0 / 1024 END AS configured_max_mb,
CASE WHEN max_size = -1 THEN 'No configured cap; engine and disk limits apply'
WHEN max_size = 0 THEN 'No growth allowed' ELSE 'Configured cap' END AS max_size_scope,
growth AS growth_raw, is_percent_growth,
CASE WHEN is_percent_growth = 0 THEN growth * 8.0 / 1024 END AS fixed_growth_mb
FROM tempdb.sys.database_files
ORDER BY file_id;Size and a fixed growth increment use 8 KB pages in the catalog. The query converts them to megabytes. Percentage growth stays a percentage, so the numeric megabyte column is NULL for those rows.
growth = 0 means no automatic increment. max_size = 0 disallows growth, and -1 removes the configured cap while engine and disk limits still apply. A positive value is the actual configured cap in pages.
Read the current instance
The historical output had an 8 MB tempdev and a 0.5 MB templog with percentage growth. Those were old instance values, not modern defaults or recommended sizing.
Use the current file list and workload to review capacity. This query does not change any file setting. The related usage, file-removal, and TempDB articles remain below.
Reference: Database file catalog.
Related reading
- SQL SERVER – TempDB Restrictions – Temp Database Restrictions
- SQL SERVER – How to Remove Temp DB File?
- SQL SERVER – Improve Index Rebuild Performance by Enabling Sort Temp DB
- SQL SERVER – Who is Consuming my Temp DB Now?
- SQL SERVER – Script to Find and Monitoring Temp DB Space Usage
- Moving Temp DB to New Drive – Interview Question of the Week #077
- SQL SERVER – Temp DB in RAM for Performance
A file listing is not a capacity diagnosis, it is the starting inventory for one.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.’
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