Most of the organizations use an SQL server to manage their vast data. The SQL server offers both automatic shrinking and auto growing of SQL database files. It causes index fragmentation in the physical files, which leads to poor performance. To avoid this problem, you have to disable auto shrink feature in SQL Server databases. It will stop automatic shrinking and further index fragmentation. Therefore, in this write-up, we are going to explain about auto grow, auto shrink and physical file fragmentation in SQL Server. Apart from this, we will also discuss the process to disable auto shrink option.
What is Auto Shrink in SQL?
This option of SQL database is used to make reuse of SQL data file. It creates a thread that runs periodically to detect database when it has more than 25% free disk space allocated. When database shrinks, it consumes system resources like CPU and disk which can severely impact server performance.
What is Auto Growth in SQL?
The auto grow option in SQL database allows you to increase your database and log files automatically when more disk space is required. However, auto growing can be another performance killer for the same reasons as auto shrinking. If you are regularly monitoring your server disk space, then you can maintain your database.
Know About Physical File Fragmentation
Physical file fragmentation in SQL database is generally caused by the auto grow and auto shrink events of the database. These are options which can be set when a database is created or altered manually or through the SQL Server UI tools i.e. Management Studio or Enterprise Manager. Physical database files that are heavily fragmented can surely affect the performance. By defragmenting heavily fragmented files, you can improve your system I/O while reading physical pages from disk.
Tip: Quick way to know How to check database fragmentation
How to Disable Auto Shrink to Avoid Performance Degradation
- Connect to the SQL Server through SSMS (SQL Server Management Studio).
- After that, expand Databases in the Object Explorer.
- Right click on the database which is affected & then choose Properties option.
- At last, go to the Options page and set the Auto Shrink property to False. After that, click on OK button to accomplish the task.
- Connect to the SQL Server using SSMS (SQL Server Management Studio).
- After that, click on the New Query option.
- Now, paste the following into the new query window:
- Then, replace “yourdb” with the actual name of the database.
- Finally, click on the Execute button.
ALTER DATABASE yourdb SET AUTO_SHRINK OFF
Database files are really essential for any organizations or users, as they store lots of crucial information. Issues like auto grow, auto shrink and physical file fragmentation in SQL Server can create trouble for users as they degrade the performance of SQL database. Disabling Auto shrink option helps you to avoid degradation of SQL Server performance.The blog discusses the method for the same.