Is it necessary to restart SQL Server after DBCC SHRINKFILE on tempdb.mdf and tempdb.ldf or is there any other method to reduce the size of tempdb.mdf and tempdb.ldf without restarting SQL Server? Is that a good idea to shrinking TempDB ? thanks
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 to
CHECKPOINT and make repeated efforts to shrink the file.
Restarting the SQL Server will return
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.
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.