Backtrack:  
 
by lunarg on May 21st 2015, at 16:30

After running complex queries that use the tempdb of a SQL Server instance, it may become necessary to shrink the database again. However, when running the shrink operation on the tempdb, it can result in the database not shrinking at all, even when the used space is minimal.

The reason for this is most likely that the clearance of the tempdb is still in cache and not flushed to disk. You need to flush the changes to disk first, after which you will be able to shrink the tempdb. Do note that flushing will most likely impact database performance, so use with caution. Also, the tempdb will not shrink beyond the initial size, configured in the file group.

DBCC FREESYSTEMCACHE('ALL')
USE [tempdb]
DBCC SHRINKDATABASE(N'tempdb', 50 )
GO

You can omit the 50 parameter in the last statement, which will shrink the file right up until the configured initial size. In this example, I shrink the file to 50% to allow for some reservation on disk for future complex queries.

 
 
« November 2019»
SunMonTueWedThuFriSat
     12
3456789
10111213141516
17181920212223
24252627282930
 
Links
 
Quote
« Debating Windows vs. Linux vs. Mac is pointless: they all have their merits and flaws, and it ultimately comes to down to personal preference. »
Me