Need to shrink tempdb as listed below on SQL 2008 R2.
SELECT name, size FROM sys.master_files WHERE database_id = DB_ID(N'tempdb') is the query I run: the results are as below. name size tempdev 4699152 templog 280248 temp1 4674904 temp2 4676800 temp3 4786752 temp4 4696496 temp5 923904 temp6 471514 Please help me in compressing this huge tempdb file. SQL Server 2008R2.
My first question is: Are you sure you want to shrink the files? 2 related questions: Why are they that large now? Will they need to be that large again? If you are sure you want to shrink the files, the following code should work. I'm assuming you want each (non-log) file to be the same size. DECLARE @TargetSizeInMegabytes int = 100; SELECT 0, 'USE tempdb;' UNION SELECT 1, 'DBCC SHRINKFILE (N''' + mf.name + ''', ' + CONVERT(varchar(20), @TargetSizeInMegabytes) + ');' FROM sys.master_files mf WHERE mf.database_id = DB_ID(N'tempdb') AND mf.type = 0 -- data rows ORDER BY 1 ; Copy the second column of the result set output, paste in the editor window, highlight, and execute.