1

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

Improve this question
2
  • 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??
    – MvdMunnik
    Jan 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
    – LeraningDBA
    Jan 25, 2017 at 19:28

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • 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
    – LeraningDBA
    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.
    – RLF
    Jan 25, 2017 at 19:38

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.