Shrinking SQL Server Databases and Log Files is a popular discussion among both experienced and entry level DBA‘s. Why? because data and log files can grow much bigger than expected and sometimes can run out of disk space. In case of High Availability deployments, you will have to run the databases under “Full Recovery Model” and that can cause your log files to grow as well. So the big question is, Should you shrink SQL Server databases for Mirroring and Availability Groups? Let’s find out!
When you shrink your data files, SQL Server recovers disk space and releases it to the file system by moving data pages from the end of the physical file to to the front where you have some unoccupied space. After when free space is created data pages are deallocated and returned back to the file system. Shrinking the database have its limitations. You will not be able to reduce the size of the data file less than the minimum size specified at creation time. You will not be able to perform this action on Databases where a Column Store index is defined.
You should “not” shrink your database files under most circumstances. Only in rare exceptions, you can do this as a onetime activity. However, as a result of this, your Index will be fragmented and you would need to rebuild them all.
First, do not press the “Shrink Button“.
Create an initial fixed database file size based on your projected estimated growth for at-least the next 2 years.
Alternatively, you can set your database size in bytes, rather than the default 10% initial size. This option is removed in SQL Server 2016. You can only define a “fixed” or “unlimited” size. See below:
The answer to this problem is quite easy. Ensure that you take regular backup of your Transaction Logs. This will help you to truncate your log files rather than shrink. Truncate will not free up disk space to the file system (Physical Disk) but will free up within the LDF file.
Maintain a backup file retention plan, so that you do not retain older files beyond a specified period. This should keep your files sizes in check.
Hope this helped!
Comments are closed.