Understanding Database Shrink Operation and Why Should We Avoid It?

Database Shrink is a process to remove unused space allocated to the database or database files to the disk. When we shrink database log file or data files, it recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. Data that is moved to shrink database files can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

There are two ways to perform shrink operation on your SQL Server databases.

Automatic Shrinking

Automatic Shrinking can be enabled by setting Auto_Shrink database property to ON. SQL Server database engine will automatically perform shrink database operation on your database whenever required. Unless we have a specific requirement, we should not set the AUTO_SHRINK database option to ON because it degrades your database performance. SQL Server shrink database files also increases IO overhead that leads to poor performance. Read below article where I have explained how this option degrades database performance and why we should not set Auto_Shrink database option to ON.

• Why Should You Always Turn Off Database Auto Shrink Property?

Manual Shrinking

We can use DBCC SHRINKDATABASE or DBCC SHRINKFILE statement to manually shrink database log file or data file within a database. Shrink database operation runs at file level. When you use DBCC SHRINKDATABASE, it also shrink the size of data or log files. We can stop the execution of DBCC SHRINKDATABASE and DBCC SHRINKFILE operations at any point in the process, and any completed work is retained.

One question most of the DBA ask that should we shrink database or should we shrink database log file or data file in SQL Server?The answer of this question is simple, we should not perform shrink operation regularly. Database Shrink operation is resource intensive operations because data pages are moved from the end of the file to unoccupied space closer to the front of the file. This process put pressure on system IO and CPU and also causes high fragmentation. You might also face some blocking issues during the shrink operation. That’s the reason it’s not recommended to shrink data file or log file on frequent or routine basis. SQL Server shrink database files should be avoided.

We should generally perform shrink operation if we truncate or drop table or tables from the database. If we remove or purge bigger chunk of data from your database then also we can perform shrink operation to reclaim the unused space to the disk. Always leave some space to the database to perform its routine operations. Most databases require some free space for regular day-to-day operations. Do Not Shrink such databases and their database files because databases will again allocate required space for their operation. Make sure to rebuild your indexes post database shrink operation if you need to perform it.

Also you should keep this in mind that we cannot shrink a database while the database is being backed up. Similarly, you cannot backup a database while a shrink operation on the database is in process. Here you have learnt about SQL Server shrink database files.
I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Read More:

• Why Should You Always Turn Off Database Auto Shrink Property?
• What is the best value for database file autogrowth?
• Fix SQL Server Error 5144 and 5145: Failed Data or Log file Autogrow Operation
• Fix Error 3023: Shrink failed for Log file