16

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.

Improve this question
2
  • 2
    DBCC SHRINKFILE does not require service restart.
    – Greg
    Jun 24, 2015 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, 2015 at 11:19

2 Answers 2

Reset to default
17

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.

Improve this answer
4
  • 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, 2015 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, 2015 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, 2015 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, 2015 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;
Improve this answer
3
  • How is your answer different than the above one ?
    – Kin Shah
    Sep 19, 2016 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, 2016 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, 2017 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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