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.

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 > 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

• 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
• SQL SERVER – ScripMoving Temp DB to New Drive – Interview Question of the Week #077t to Find and Monitoring TempDB Space Usage
• SQL SERVER – Temp DB in RAM for Performance

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)