We know you will not like it, but, shrinking databases is a bad idea. Ask any experienced DBA or a database architect and he/she will tell you why. You can only make it a one-time exception and proceed with it. However, reoccurrence of this operation on a repeated basis is going to cost you both operationally as well as from the performance perspective. With that expectation being set, let us go ahead with the discussion about bow to Shrink SQL Server Databases for Mirroring & Availability Groups.
Shrink SQL Server Databases for Mirroring & Availability Groups
Shrinking SQL Server Databases and Log Files is a widespread discussion among both experienced and entry level DBA. Why? It is because data and log files can grow much bigger than expected and sometimes can run out of disk space. In the case of High Availability deployments, you will have to run the databases under “Full Recovery Model, unoccupied” 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!
What does “Shrink a Database” do?
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 the front where you have empty 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.
Should you Shrink your SQL Server Data Files?
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.
What is the recommended best practice?
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:
What to do when your Transaction Log runs out of Disk Space?
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.
See what other SQL Experts from the Industry are saying:
- Stop Shrinking Your Database Files. Seriously. Now
- Paul Randal – “Why You Should Not Shrink Your Data Files”
- Mike Walsh – “Don’t Touch That Shrink Button”
- Mike Walsh’s followup – “Shrinking is a Popular Topic”
- Kimberly L. Tripp – “8 Steps to Better Transaction Log Throughput”
- Gail Shaw – “Shrinking Databases”
- Tom LaRock – “When to Use AutoShrink”
Hope this helped!