Should I schedule a job to shrink my databases automatically?


We know SQL Server provides us capability of automatically shrinking a database or data files/log files separately in various ways. But the bigger question is “should I do this or not?”

While automatically shrinking database or any associated file is beneficial to reclaim some unused space, we need to note that shrinking operations takes significant resource and time. Also significant resource and overhead is consumed to grow a file.

So based on your specific situation, I recommend to take below approach:

  1. If you are planning to auto shrink a production database (or a database which is in high demand), then “Do not do it”. Unnecessary shirking will cause production overhead which will be possibly followed by expensive file growth operation soon. Rather rely on your day to day operations manual and monitoring systems to manage space.
  2. If you are thinking to auto shrink a database which is not in high demand and not in production use, then also better “Do not do it”. However you may place “Auto Shrink” only as a “temporary workaround” if you have absolutely no other option. Be sure to remove “Auto shrink” as soon as possible to minimize unnecessary overhead to server.

If you are looking for a single line answer to this question “Should I schedule a job to shrink my databases automatically?”, I will then recommend “Do not schedule anything to shrink a database or associated data files automatically on a scheduled basis”

Comments