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
-
Please don't shrink it or any DB/db file for that matter... Get into the habbit to adequatly pre-size your files and databases and if possible manually grow them. Set the max size parameter per file to prevent out-of-control growth. Why do you have the need to shrink them currently??– MvdMunnikJan 25, 2017 at 18:17
-
i ran DBCC CHECKDB for database sanity check .i got error Tempdb out of space .after running DBCC CHECKDB mdf and ldf files are eaten up space and getting bigger .@MvdMunnik– LeraningDBAJan 25, 2017 at 19:28
1 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 to CHECKPOINT
and make repeated efforts to shrink the file.
Restarting the SQL Server will return tempdb.mdf
(and tempdbxx.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.
-
i am really low on space .if i set up autogrowth or manually growth do i have to restart server ?do you think its a good idea /@RLF Jan 25, 2017 at 19:33
-
Growth and shrinking of files do not require a restart of a server. This assumes that you have the space already available on your server.– RLFJan 25, 2017 at 19:38