asked
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.
The simple answer is to use DBCC SHRINKFILE(N'filename', 100) to shrink a file to 100 megabytes. I use scripts like the one I wrote in the answer to simplify many admin tasks so I don't need to rewrite the same steps each time on multiple servers.
1. Yes. I want to compress them as the current max is 350 GB. 2. They are large because one instance is having multiple databases (including erp db,cognos, legacy database etc.) 3. That is the million dollar question. I do a sql restart with permission and they are back 53 GB. After 5 days, they are back in 250 GB domain.
You need to address number 3 then first. Something needs 250GB of tempdb space, so shrinking the files will only cause you performance issues as they grow again. Once you have the growth under control, then you can think about shrinking the files.
When I run the DBCC Shrinkfile(N'temp2',100); Here is the error I get. File ID 4 of database ID 2 cannot be shrunk as it is either being shrunk by another process or is empty. Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. so it seems that another shrink job is running and finished yet. Is there any way to find that process.
Try this to get some information about another shrink possibly running. SELECT s.original_login_name, s.session_id , r.blocking_session_id, r.start_time, r.total_elapsed_time , sql.text FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_requests r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) sql ;
[link text][1] [1]: /storage/temp/2964-book1.xlsx I uploaded the result. Should I just kill the session-id 54.
18 People are following this question.
