15

Is it necessary to restart SQL Server after DBCC SHRINKFILE on tempdb.mdf or is there any other method to reduce the size of tempdb.mdf without restarting SQL Server?

Kindly help as I need this for a production server and I'm hoping to do the shrinking without any down time.

migrated from stackoverflow.com Jun 24 '15 at 11:13

This question came from our site for professional and enthusiast programmers.

  • 2
    DBCC SHRINKFILE does not require service restart. – Greg Jun 24 '15 at 5:22
  • You cannot shrink the tempdb as it is in usage. And yes there is some solution for this but it's not very clean and has some side effects. – Ionic Jun 24 '15 at 11:19
15

You can do that this way:

-- write everything from your buffers to the disc!
CHECKPOINT; 
GO
-- Clean all buffers and caches
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;
GO
-- Now shrink the file to your desired size
DBCC SHRINKFILE (TEMPDEV, 40960);
-- Make sure that there is no running transaction which uses the tempdb while shrinking!
-- This is most trickiest part of it all.
GO

The last step is the most trickiest. During the shrink process, no other action should use the tempdb, as this could cause an abort of your SHRINKFILE operation. Due to the fact that the tempdb is quite easy to shrink, it shouldn't take to long to shrink it.

Beware that this is something like a "soft restart". Everything will be removed from the buffers and written to the disc. This means an impact on your I/O subsystem (write) as it have to handle all the write operations. After that you can shrink the file (which has an impact on read and write performance) and at the end, all processes which query any table will need to retrieve the data back from the I/O subsystem into the buffers. This may hurt more than an restart.

If you're running a development system, you should just restart the machine instead of this way. But on some production systems without a failover partner, this may be useful.

  • thanks Ionic for nice explanation. tempdb.mdf is of 46 GB in my case. If no transactions are going on then can i shrink it to 100%. Kindly suggest. – Manii Jun 25 '15 at 11:29
  • Yes the 40960MB is just an example. On my system the tempdb runs normally at ~50GB and in rare cases grows to 200GB. :-) – Ionic Jun 25 '15 at 12:45
  • <pre>I run the query as DBCC SHRINKFILE (tempdev, 1024);</b></b> And it successfully freed up the space but again i run it like</b></b> I run the query as DBCC SHRINKFILE (tempdev, 30000);</b></b> And it gave me result as below :</b></b> DbId Field Current Size Minimum Size Used Pages Estimated Pages</b> 2 1 5699352 1024 696 696</b></b> But as checked tempdb size was not reduced.</pre> – Manii Jun 27 '15 at 1:54
  • This message may occur if a transaction uses the tempdb while shrinking. It may also be that your defined shrink size (~30GB) is above the free limit of your your tempdb. – Ionic Jun 27 '15 at 11:36
1

You can just go with below step only

USE tempdb;
GO;
dbcc freeproccache;
DBCC SHRINKFILE (tempdb_file_name, memory_in_MB);
GO;
  • How is your answer different than the above one ? – Kin Shah Sep 19 '16 at 13:33
  • 1
    Hello Kin, Tempdb File size will be reduced with DBCC FREEPROCCACHE; and then shrink the tempdb file. We don't need below statement for shrink tempdb. DBCC DROPCLEANBUFFERS; DBCC FREESYSTEMCACHE('ALL'); DBCC FREESESSIONCACHE; – JERRY Sep 20 '16 at 9:18
  • 1
    This is the simpler answer, in most cases only the DBCC FREEPROCCACHE is needed to allow you to shrink the tempdb. See related question – James Jenkins Nov 7 '17 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.