You've got two different questions in here:
Q: Sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.
A: When that happens, all of the TempDB data files will grow roughly equally, so you won't have to worry about shrinking specific ones. Frankly, you don't want to shrink them - if you've got drive space set aside for TempDB, just leave these files in place. Why keep re-fighting the same battle? You'll have a rogue developer run another query in a few weeks. Just leave this in place. You don't get bonused based off empty drive space.
Now, having said that, if you've got TempDB's data and log files on the same volume as your user databases (or heaven forbid, the boot drive), then that's the real root cause, and you need to fix that. Even if you don't put them on separate spindles, TempDB should be on a separate logical volume to mitigate this exact problem. When it fills up, it fills up - but it doesn't take user databases (or the entire server, in the event of a full C drive) offline.
Q: (DBCC SHRINKFILE) seems to not work very often in 2008
It's more a function of how SQL Server relies more and more on TempDB in each release. When something's active in TempDB, you can't move its data around, and each new version of SQL Server works more in TempDB. For example, when you enable Read Committed Snapshot Isolation, the version store it uses lives in TempDB. When you use AlwaysOn Availability Groups, it tracks user database statistics in TempDB too. This is just another reason why you set aside a logical volume for TempDB, size the data files to fill it up, and then walk away - your work here is done, and don't try to shrink those files.
Best Answer
It is not normally recommended to do
DBCC SHRINKFILE
unless you are dealing with a severe space shortage.No, it is not necessary to restart SQL Server after a
DBCC SHRINKFILE
operation. A very busy database, which is certainly true of tempdb, can be shrunk, but not always easily. You may need toCHECKPOINT
and make repeated efforts to shrink the file.Restarting the SQL Server will return
tempdb.mdf
(andtempdbxx.ndf
files),templog.LDF
to their defined start size. Likely you would not want to do that on an active server during times of heavy load.Your database's data and log files should be ideally defined to match the work you are doing on the server, with some extra buffer.
Although
DBCC SHRINKFILE
puts an extra burden on your server and adds to fragmentation problems, you may decide to do so if needed to recover space without a restart.Usually though, this can be managed by doing log backups at a frequency that keeps the log files smaller than the space allocated. Also, adding more resources to the server would likewise reduce the occasions where shrinking files might be needed.