asked
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][1] 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][2] 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? [1]:
http://dba.stackexchange.com/questions/44943/transaction-log-maintanance-while-using-alwayson-availability-group [2]: /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!
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 `-1` with growth 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.
It's perfectly fine to shrink a database/file, as long as you understand the ramifications of doing so. Given that the file will grow until it consumes the disk in ~8MB chunks, I would probably support shrinking the file. I would think that shrinking down to 5% (~6GB) would be sufficient enough, however you will have to make that decision based on your known workload. I would also assume that you're now taking appropriate log back ups. ;-) Contrary to popular belief, it's really OK to shrink when the conditions are right. ;-)
15 People are following this question.
