Will DB Shrink in SQL Server affect database performance?

Will DB Shrink in SQL Server affect database performance?


In a database, based on the initial allocation or extension and subsequent operation on tables, objects will hold more unused space than required.

Shrinking the database to a minimum required and releasing most of the unused space is possible. This may be a positive move as the larger freed space will improve performance. But shrinking may affect your database performance significantly, if not properly done. Shrinking will increase fragmentation and will cause any DB operation costly. Rebuild indexes is necessary after DB shrink to reduce fragmentation and increase performance.

Also, the cost of file size expansion, like for accommodating additional records, is too high. Hence frequent file size expansion for requirements due to file size increase settings can also significantly affect performance. We had one such instance of frequent DB Shrink to least possible size and expansion of the file is also was set to very less value causing significant performance issue and had to firefight every day. We studied the requirements, removed the auto DB shrink process and started monitoring the space availability.

DB shrink is done only when required and also the minimum file size is set in order to make sure the file size expansion may not be required for at least for defined period.

Read More


Stay updated with us

Join Sennovate today and get updates on news, events and many more