Shrink SQL Server Databases for Mirroring & Availability Groups

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 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!

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 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.

Shrink SQL Server Databases for Mirroring & Availability Groups

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.

Feat-4

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:

Shrink SQL Server Databases for Mirroring & Availability Groups

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.

 

Shrink SQL Server Databases for Mirroring & Availability Groups

See what other SQL Experts from the Industry are saying:

Hope this helped!

Disclosure: We are a professional review website that sometimes receive compensation or free units from the companies whose products we review. We test each product thoroughly and give high marks to only the very best. We are independently owned and the opinions expressed here are our own.