Will DB Shrink in SQL Server affect database performance?

April 1, 2019

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.

Related Articles

Why Privilege Access Management Matters?

Why Privilege Access Management Matters? When it comes to Privilege Access Management. The first thing that pops up in my mind is a vault. Simply put, Privilege access management is the gateway to the most valuable digital assets in an organization. But before diving...

Cyber Security in Construction

Cyber Security in Construction Irrespective of the nature of business cyber-attack is quite common and a nightmare to any organization. The breach of sensitive personal data of an organization is making headlines quite often. Recently, Architectural and Construction...

Stay Up to Date With The Latest News & Updates

Join Our Newsletter

Unleash more of your potential with weekly updates, tailored for your team.