TempDB–What size was it?

Published 08 May 11 05:04 PM | MartinCroft

Day 8. As you might know TempDB is created every time SQL server service is restarted, but do you know what size it will start at next time you start SQL? Well this script shows you, its amazing how many production servers don’t get sized from the default when SQL was installed and its quite suspiring just how small that is, 8Mg!, So if you have an 8Gb data file set to 10% growth, guess how many times it takes 8Mg to get to 8Gb growing by 10% ( Lots), which can cause performance problems.

SELECT
  alt.filename
  ,alt.name
  ,alt.size * 8.0 / 1024.0 as originalsize_MB
  ,files.size* 8.0 / 1024.0 as currentsize_MB
FROM
  master.dbo.sysaltfiles  alt 
INNER JOIN 
  tempdb.dbo.sysfiles files ON alt.fileid = files.fileid
WHERE
  dbid = db_id('tempdb')
Filed under:

Comments

No Comments