Scenario: On SQL 2008R2 server, Tempdb has unlimited growth max size and is in danger of filling disk.
Need a quick way to set max size to current size to prevent further growth until can assess if there is an abnormal process taking up TempDB, what optimal size of TempDB should be, and/or disk space can be added.
SELECT ‘ALTER DATABASE [‘ + db_name(A.database_id)
+ ‘] MODIFY FILE ( NAME = N”’ + A.name
+ ”’, MAXSIZE = ‘
+ cast((B.size_on_disk_bytes/1024) as varchar) + ‘ KB)’
, A.*
, B.*
FROM master.sys.master_files A
JOIN sys.dm_io_virtual_file_stats( db_id(‘tempdb’),NULL) B
ON A.file_id = B.file_id
WHERE A.database_id = db_id(‘tempdb’)