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

How to Avoid a Data Breach

How to Avoid a Data Breach Government agencies are becoming prime targets for cyber attackers, they hold data for ransom. Whatever the rationale of the cyber attackers, it is imperative that government agencies need to pull up their socks to understand the gravity of...

Real ID-Compliant – A Must for all Residents

Real ID-Compliant – A Must for all ResidentsStarting October 1, 2020, every State and Territory Resident will need to present a REAL ID-compliant license/ID or another acceptable form of identification (like a passport or passport card) for boarding commercial...

Stay Up to Date With The Latest News & Updates

Join Our Newsletter

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