Transaction Log Maintanance While Using AlwaysOn Availability Group
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: ![alt text] 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). Also, why the transaction log size is not reduced when daily back ups are done on the secondary? :
http://dba.stackexchange.com/questions/44943/transaction-log-maintanance-while-using-alwayson-availability-group : /storage/temp/1631-x.png
I'd say neither. Just curious, why are you looking to shrink the transaction log to begin with? Are you having space issues? If the transaction log grew to that size, then it needed it for a reason thus it might need it again. When it does, it could be an expensive operation to grow the log file back out to accommodate the transaction. Even if you have instant file initialization turned on, SQL Server still has to zero out the log file which will take time. In regards to your other question, the backup of the transaction log doesn't reduce the physical file as that requires a 'SHRINK' operation. In a sense, the backup is only handling the data contained within the log file itself, not the size of the physical container. Why it was designed this way specifically, I can't answer. ;-) Hope this helps!