October 3, 2016 at 11:35 am
Hi all,
I understand that mirroring is no longer recommended but the setup exists in my current environment
I am trying to understand the portion on log truncation between alwayson availability group and database mirroring. (assume there is just a 1 primary database to 1 mirror/secondary replica)
q1) In alwayson, the backing up of the transaction logs in a single replica truncate both the primary and secondary replica's database transaction logs.
Does it applies to database mirroring as well ?
q2) if not, does that implies that we have to do transaction log backup at the mirror database as well ? so as to keep the transaction log size from growing constantly ?
Regards,
Noob
October 3, 2016 at 12:17 pm
szejiekoh (10/3/2016)
Hi all,I understand that mirroring is no longer recommended but the setup exists in my current environment
I am trying to understand the portion on log truncation between alwayson availability group and database mirroring. (assume there is just a 1 primary database to 1 mirror/secondary replica)
q1) In alwayson, the backing up of the transaction logs in a single replica truncate both the primary and secondary replica's database transaction logs.
Does it applies to database mirroring as well ?
q2) if not, does that implies that we have to do transaction log backup at the mirror database as well ? so as to keep the transaction log size from growing constantly ?
Regards,
Noob
1) Do not be worried about database mirroring being deprecated. I guarantee you I will still be using it at clients AT LEAST 10 YEARS FROM NOW because it still exists in 2016. I will probably be using it 15+ years from now because I still have clients on SQL 2000. 🙂
2) As long as you are doing tlog backups, stuff that is able to be flushed from tlog will be, and from both sides.
3) I don't believe you can backup a secondary mirror database.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 3, 2016 at 12:45 pm
From what I know, I believe Kevin's advice.
October 3, 2016 at 11:36 pm
TheSQLGuru (10/3/2016)
szejiekoh (10/3/2016)
Hi all,I understand that mirroring is no longer recommended but the setup exists in my current environment
I am trying to understand the portion on log truncation between alwayson availability group and database mirroring. (assume there is just a 1 primary database to 1 mirror/secondary replica)
q1) In alwayson, the backing up of the transaction logs in a single replica truncate both the primary and secondary replica's database transaction logs.
Does it applies to database mirroring as well ?
q2) if not, does that implies that we have to do transaction log backup at the mirror database as well ? so as to keep the transaction log size from growing constantly ?
Regards,
Noob
1) Do not be worried about database mirroring being deprecated. I guarantee you I will still be using it at clients AT LEAST 10 YEARS FROM NOW because it still exists in 2016. I will probably be using it 15+ years from now because I still have clients on SQL 2000. 🙂
2) As long as you are doing tlog backups, stuff that is able to be flushed from tlog will be, and from both sides.
3) I don't believe you can backup a secondary mirror database.
Hi Kelvin, Steve
Thanks for your replies.
q1) do you mean that we are able to do transaction logs backup in the mirrored instance and the transaction logs in primary will get truncated as well ? How does that happen ?
q2) to side track abit, can I check for the shrink database task in maintenance plan wizard, does it includes shrinking of the transaction logs ?
q3) if the answer to q2) is yes, if I have a database with recovery mode set to FULL, but I did not do any logs backup, will the shrink still work on the transaction logs ?
( my understanding is that the shrink will not work as truncation of the log does not take place) - please correct me if I am wrong ?
Regards,
Noob
October 4, 2016 at 5:20 am
szejiekoh (10/3/2016)
TheSQLGuru (10/3/2016)
szejiekoh (10/3/2016)
Hi all,I understand that mirroring is no longer recommended but the setup exists in my current environment
I am trying to understand the portion on log truncation between alwayson availability group and database mirroring. (assume there is just a 1 primary database to 1 mirror/secondary replica)
q1) In alwayson, the backing up of the transaction logs in a single replica truncate both the primary and secondary replica's database transaction logs.
Does it applies to database mirroring as well ?
q2) if not, does that implies that we have to do transaction log backup at the mirror database as well ? so as to keep the transaction log size from growing constantly ?
Regards,
Noob
1) Do not be worried about database mirroring being deprecated. I guarantee you I will still be using it at clients AT LEAST 10 YEARS FROM NOW because it still exists in 2016. I will probably be using it 15+ years from now because I still have clients on SQL 2000. 🙂
2) As long as you are doing tlog backups, stuff that is able to be flushed from tlog will be, and from both sides.
3) I don't believe you can backup a secondary mirror database.
Hi Kelvin, Steve
Thanks for your replies.
q1) do you mean that we are able to do transaction logs backup in the mirrored instance and the transaction logs in primary will get truncated as well ? How does that happen ?
q2) to side track abit, can I check for the shrink database task in maintenance plan wizard, does it includes shrinking of the transaction logs ?
q3) if the answer to q2) is yes, if I have a database with recovery mode set to FULL, but I did not do any logs backup, will the shrink still work on the transaction logs ?
( my understanding is that the shrink will not work as truncation of the log does not take place) - please correct me if I am wrong ?
Regards,
Noob
A1) I specifically said you cannot do backups on a mirrored database. Steve agreed.
A2) DO NOT SHRINK A DATABASE AT ALL!!! Binoogle it - BAD STUFF happens.
A3) Re-read A2 until your brain will not let you think about shrinking a database. There are very few exceptions to this mandate.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 4, 2016 at 11:09 am
Hi Kevin,
Thanks for your reply. I understand where you are coming from, but the setup is done via an external vendor and I need to get my facts right before telling them that their setup is not "recommended"
Just to make sure that my understanding is correct , shrinking of transaction logs required the transaction logs to get truncated 1st (backup-ed 1st) - right ?
Hope to hear from you soon.
Regards,
Noob
October 4, 2016 at 2:43 pm
Shrinking a tlog file that is full of stuff is extra silly. It could be partially full due to a variety of reasons and artifacts about how logging and flushing work. But say you are in FULL recovery mode and are just taking FULL backups - shrink won't help because everything still sits there in the tlog.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 4, 2016 at 2:43 pm
use this to see how big and how full your tlog files are:
DBCC SQLPERF(LOGSPACE)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 6, 2016 at 11:13 am
Hi Kevin,
Sorry for the late reply.
Thank you so much for your confirmation.
Regards,
Noob
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply