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