In this article, we will discuss the SQL Server Auto Shrink database property, which allows SQL Server to automatically shrink the database files when the value is configured as True in the database option. Automatic shrink operation is done by the server auto shrink database feature, which is the main course of this article.
To avoid the activity of shrinking of data/log files whenever file size surpasses the free space in the data file, DBA (Database Administrator) must back up the log files at a regular interval. Backing up the whole database is not a good idea; one must construct or set up the transaction log also. Transaction logs will increase in numbers until it occupies all the available disk space if you don’t take back up of them. If you back up the database, it releases the free space to be reused again. Database Administrator must schedule a transaction log backup to reduce the size of log files at a fair size.
SQL Server Auto Shrink feature is disabled by default on SQL Server instance databases. In the scenario where you have a number of relatively small databases that grow to a larger size either by insertion of new tuples or by a large number of empty spaces created by deletion of several tuples, SQL Server Auto Shrink becomes pretty handy in this situation. Moreover, you must need not worry about the size fragmentation of database files.
While shrinking the database, you must have to consider shrinking operations on both critical as well as relatively larger databases as a part of maintenance. Besides, this one should avoid running manual shrink feature; the consequences of doing this are that one will never be acquainted with issues regarding the new or existing request. However, Transaction files shrinking is better than the data files shrinking.
Way to activate and deactivate the SQL Server auto shrink for the database?
Users can enable and disable database auto shrink option using SSMS and T-SQL both ways.
Activating the database auto shrink by using SSMS:
Users can enable or disable this option from the properties of the database, with a tag of Auto Shrink. Here, True in the drop-down will enable this option for your database.
Enable database auto shrink using T-SQL:
Users can execute below T-SQL statements to enable or disable the shrink database. We have used the AdventureWorks database for the above T-SQL statement.
|
1 2 3 4 5 6 |
--Enable Auto Shrink for the database AdventureWorks ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON GO --Disable Auto Shrink for the database AdventureWorks ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF GO |
Effects of database shrinking on the query performance
Bad effects can be raised on the query performance side if you turn on the database auto shrink option and auto-growth settings together for the database. We can keep some room for the data files to grow and also avoid some frequent auto-growth events by setting up the database size to an optimum value or mostly every database have some parameters with auto-growth features enabled.
For such databases, auto shrink features must be activated by us when the database is smaller, one and no more CRUD operation, so it can make shrinking of data files possible and retrieve the free space that we provisioned purposefully to keep away our databases from auto-growth events. Free space will be released automatically in the data files and log files periodically in round-robin sequential priority by the Auto shrink feature if multiple databases have turned on this feature.
For large-sized databases, auto-growth, and thereafter, the auto shrink will be executed that will result in performance issues lead by system-level fragmentation. To sum up all these, for any database, auto shrink should not be activated based on the following reasons:
- Executing the SQL Server auto shrink algorithms without any purpose will definitely going to squander the resources in a massive quantity
- Whether you perform SQL Server auto shrink or manual shrink, it will obviously be going to cause the index fragmentation and this eventually executes the data files shrinking also
- If the server is also forcing the boundaries of the IO subsystem, running shrink may push it over, resulting in long disk queue lengths and possibly IO timeouts, this will eat up Server IO and CPU resources in a massive quantity
- System performance will be retarded by file system disk-level fragmentation, which is again the direct effect of doing shrinking and growing data files often
To understand more about Growth and Shrink Events in SQL Server, read this article, Get details of SQL Server Database Growth and Shrink Events.
For the critical database, manual shrink operation could be run at the database file level by the expert. Manual shrink file activity can be done when the delete operation is executed, and thereafter, space has been reclaimed. We must rebuild the fragmented indexes upon execution of shrink operation as shrink operation can lead to index fragmentation. Index fragmentation percentage could be checked by the user with the use of T-SQL statements of DMV’s. However, shrinking the log file should be manually done as and when requires and shouldn’t be part of regular maintenance activity.
To monitor the disk usage regular basis, the disk usage report could be analyzed by the user before performing the manual shrink operation using SSMS, which gives you an insight of data and log space information for a database in the display. If the user would like to get a dashboard for a calculation of Reserved Space and Free Space for database files, then Disk Report becomes very helpful. However, the disk report inhabits the information using SQL Server DMV’s. The disk report is available on the below directory using SSMS.
Database >> Reports >> Standard Reports >> Disk Usage
Here, we have a Disk Report for the [AdventureWorks] database. Real-time statistics for the data files and log files could be seen by the user. This report incorporates primary information of Total Space Reserved, Data Files Space Reserved, Transaction Log Space Reserved, and In-Memory OLTP Space Reserved.
Shrink database automatically uses SQL Server Jobs
Such scripts could be performed by scheduling a job by the user to execute the database shrink operation with scheduler activity. As the first step in the script, find the free space in the database file and then shrink that file if defined criteria for the free space get matched. Free space should be calculated with the help of SQL Server DMV’s.
Basically, database file monitoring is performed by the database administrator, which in turn steps on the database to monitor the database file size. If the pre-defined boundary is crossed by any file, then they need to perform such steps. So that activity can be automated using SQL Server job to perform on a daily basis, weekly or monthly at a predefined time.
The best practice is to schedule this job on the database log file only and monitor the data file free space manually. Because it can affect query performance as well. SQL Server DMV will return the data file and log file in the same result set, so file type needs to bifurcate in the T-SQL statement of SQL Server DMV. Before shrinking the file, the user can apply query logic on the total space and free space of the log file. If calculation criteria are matched with the file property, then the file will shrink with the target file size.
Check free space for the database files:
|
1 2 3 4 5 |
SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files; |
If such criteria matched with the database files, for example, if Free space is greater than (n) MB/GB, if n(%) free space compare to total space, and many more. When free space is not available for the log file and file size is near about the max file size parameter value, then the database administrator needs to scout in the transaction log.
Shrink database file:
|
1 |
DBCC SHRINKFILE(file_name, 5120); |
Here, 5120 is a target file size in MB. So, the file will become a size of 5120 MB. Set up the T-SQL Query with required criteria in the SQL Server job step and schedule it on-off hours.
SQL Server auto shrink option cannot be enabled for all databases. Specifically, it helps for the smaller database, which performs less CRUD operation comparatively.
Conclusion
In this article, we discussed the Auto Shrink database property in SQL Server to shrink data and log files and remove unused space. Shrinking a database is an expensive operation and should be used carefully.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020