Managing mssql tempdb database and files

If tempdb is set to auto grow, then it will grow only to fill the disk it is in and no more.

So I have created a 1GB ram disk (http://memory.dataram.com/) and set the tempdb to be on the ram disk. This has significantly improved the performance of mssql although I thought tempdb is supposed to be in ram most of the time anyway!!!

The below allows you to see current details of tempdb and files and how they are set to grow:

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

An the below, lets you specify the exact size of the data and log files and how they grow. The values in the below are all in Mega Bytes.

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 900 , FILEGROWTH = 50)
go
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 50 , FILEGROWTH = 5)
go

If you want you can set FILEGROWTH to 0 which means it will not grow but this is not required as the tempdb will never grow past the desk space it lives on.