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.

more ▼

asked Oct 22, 2015 at 04:33 PM in Default

avatar image

731 4 10 16

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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;'
 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

Copy the second column of the result set output, paste in the editor window, highlight, and execute.

more ▼

answered Oct 22, 2015 at 05:03 PM

avatar image

Tom Staab ♦
14.5k 7 14 21

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.

Oct 22, 2015 at 05:07 PM Tom Staab ♦
  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.

Oct 22, 2015 at 05:13 PM vivekchandra09

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.

Oct 22, 2015 at 06:28 PM Kev Riley ♦♦

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.

Oct 22, 2015 at 05:49 PM vivekchandra09

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
Oct 22, 2015 at 06:31 PM Tom Staab ♦
(comments are locked)
10|1200 characters needed characters left

link text

I uploaded the result. Should I just kill the session-id 54.

book1.xlsx (9.3 kB)
more ▼

answered Oct 22, 2015 at 07:51 PM

avatar image

731 4 10 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 22, 2015 at 04:33 PM

Seen: 101 times

Last Updated: Oct 22, 2015 at 07:51 PM

Copyright 2017 Redgate Software. Privacy Policy