DBCC SHRINKFILE for Log and Data when using Availability Groups

Question

We had an issue where one of our database tables grew by 40GB by mistake. The database is part of an Availability Group with one primary and two replicas. While trying to delete the extra records we also managed to explode our log file sizes by ~70GB (Side note: always delete in small batches!) which started causing space issues on our primary and replicas. We were able to survive that by live-expanding the drives on the VMs and waiting for replication to finish, but now that the records are removed we would like to reclaim the unused space in the data and log files so it can be used by other databases.

How do you use DBCC SHRINKFILE for databases that are part of an Availability Group?

Answer 1

Denis P. wrote a basic SQL Script to resize the log files in an AG, so here is the overall process:

1. Make sure replication has finished, as you can't shrink the files while the logs are still being sent to a secondary replica
2. Figure out what new size you want the data and log files to be. There are plenty of SQL scripts to find this information, or you can use the database properties or Shrink File Task wizard in SSMS to figure out how much space is still in use.
3. On the primary use the script below to resize your files. It may require multiple executions to take effect.
4. Once the primary files have the correct size and the replication queue has stabilized, you may need to restart the SQL Server service on the secondaries for the new file size to take effect on those systems.

TSQL Scripts:

--Run on primary to shrink db file to 10000 MB. May need to run multiple times.
DBCC SHRINKFILE (N'MyDatabaseName', 10000, NOTRUNCATE)
DBCC SHRINKFILE (N'MyDatabaseName', 10000)

--Check data file size
USE Master
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MyDatabaseName'
ORDER BY SizeMB DESC

--Once data file is resized and replication has finished, start resizing log file

--Sorted view of log file sizes and percent used
USE MyDatabaseName
Drop table IF EXISTS #tmplogs
CREATE TABLE #tmplogs (
  DatabaseName varchar(100)
  , LOGSIZE_MB decimal(18, 2)
  , LOGSPACE_USED decimal(18, 2)
  , LOGSTATUS decimal(18, 0)
)
INSERT INTO #tmplogs EXEC('DBCC SQLPERF(LOGSPACE);')
SELECT * from #tmplogs ORDER BY LOGSIZE_MB DESC, LOGSPACE_USED DESC


--Run on primary to shrink log file. You must take a backup of the log file before it can be truncated
BACKUP LOG MyDatabaseName TO DISK = '\\Backups\SQL\_Trans\My_AG\MyDatabaseName\MyDatabaseName_LOG_20170721_200008.trn' WITH NOFORMAT, INIT, NAME = N' Trn Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
DBCC SHRINKFILE (N'MyDatabaseName_log' , 2000, NOTRUNCATE)
DBCC SHRINKFILE (N'MyDatabaseName_log' , 2000)

--Again this may require multiple executions to remove unused space
--Also the file size on the replicas may not decrease until the SQL Server service is restarted on those systems

And of course once you are finished you may have index fragmentation or other performance issues that can occur when using DBCC SHRINKFILE. In our case this is a small database where we don't care too much about the performance and there are weekly jobs to handle index maintenance, so we would rather reclaim the unused space.

The script helped us reclaim a total of over 250GB of unused data and log file space!