Denis P. wrote a basic SQL Script to resize the log files in an AG, so here is the overall process:
- Make sure replication has finished, as you can't shrink the files while the logs are still being sent to a secondary replica
- 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.
- On the primary use the script below to resize your files. It may require multiple executions to take effect.
- 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.
--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
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
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
Drop table IF EXISTS #tmplogs
CREATE TABLE #tmplogs (
, 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!