Why to Prevent Shrinking Data Files In SQL Server?
A few days earlier, we were approached by a client who had a SQL Server database of very large size and it was surpassing their disk space. The first thing that came to our mind was to shrink the size of the complete database and to retain the space (20 GB) that was not in use anymore. The shrinking data files in SQL Server was initiated and it took a considerable amount of time. When the process finished, what we got in the end was totally opposite to what we had expected. The performance of the database had got worse than before, even though its size was decreased.
This is when I realized that shrinking data files in SQL Server, is definitely not an option you should opt for when your database size increases.So, here I will share my experience with you and alert you that shrinking the database is the worst decision you will ever make in your life. The obvious question you will be having by now is “Why you should not shrink your data files?” The simplest and critical answer to this is that shrinking data files in SQL Server increases fragmentation and reduces the overall performance of SQL Server.
Negative Aspects of Shrinking Data Files in SQL Server
The auto-shrink option when enabled automatically helps in shrinking data files in SQL Server at regular intervals of time. Not only this is hazardous but also it is the most dangerous thing that you are doing to your SQL Server database.This can be summarized in the below-mentioned steps:
1 .Causes Fragmentation
The algorithms that are employed in the shrink command are usually of brute force. The algorithms starts the shrinking process at the end of file, retrieves the allocation pages and moves the pages in the starting of the file. This procedure is fine in case no indexes are present. However, in case the indexes are present, the clustered or non-clustered index leaf-page is moved by shrink and fragmentation occurs.
2. Uncontrolled Procedure
A user cannot control when the shrink operation will start. It can start anytime and will try to shrink one of the databases on which the AUTO_SHRINK command has been turned on.
3. SQL Server Functioning
It effects the overall performance of the SQL Server.It does not have any long-term harmful effects on the database, but it does consume many resources like IO and CPU, thus effecting other processes as well. In addition to this, it also slows down the functioning as it moves the data through buffer many times and thus moves the hot pages to the disk.
4. Vicious Cycle Of Auto-Grow & Auto Shrink
If you are using auto-shrink, you will definitely get your database into a vicious cycle of auto-grow then auto-shrink and then again auto-grow and so on.An active database always requires some free space for normal functioning. Therefore, when you are shrinking that space also, the database is likely to grow.
Bottom Lines
To conclude, I would just like to say that if you have been thinking to shrinking database in SQL Server, then please drop this idea right away and try other methods to free up the space.