We are using
AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database.
I have read here doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable. Anyway, the transaction log backup size is big and can be reduce using shrink file:
I have restore the database locally and perform the shrink operation. The log file size was reduces to 160 MB.
My question is on which database should I perform a shrink operation over the transaction log file (primary, secondary or both)?
I guess in the past for several years no back ups of the log file are made, so it become so huge. Executing
DBCC SQLPERF (LOGSPACE) I can see that only
0.06% of the file are used - there is no point for me to keep such huge size of the log file. In
[sys].[database_files] I check that its
max_size is set to
65536 so I guess when it need more space it will get. Anyway, I can shrink it to 5% for example in order to prevent future growth. I am trying to find some confirmation that I it is not bad idea to do so.
Actually, back ups (on the database and the log files) are performed only on the secondary databases, so it will be easier to perform the shrink file on them, but will the primary log file size be reduced as well?