July 7, 2012 at 5:31 am
Hi every body
I read BOL and many pages but i am so confused :
1_ what happen to the unsent log and redo queue in database mirroring with no witness and High Protection mode :
a_ when connection between 2 partner is broken(principal is still working)?
b_ when principal is down?
2_ If mirror is fail and it can't send acknowledgement to principal,what happen to the logs in principal?I mean do the logs in principal should wait for acknowledgement from mirror to COMMIT those logs on both principal and mirror?
July 7, 2012 at 7:28 am
The log grows until mirroring is reestablished or the log file is full. The log cannot be truncated (marked for reuse) until the log records are hardened at the mirror, so until the mirroring is re-established or removed, the log will grow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2012 at 10:13 pm
thanks for your reply
so if the principal fails and we do a manual failover,i should do a forced service failover because the mirror database is now Disconnected and in Recovery mode.so we dont have unsent log any more?!it means that we loose the unsent log?
and what about second question ?if mirror cant send acknowledgement to principal,they wont be committed either in mirror and principal ?
because principal waits for acknowledgement from mirror and then commit and send to clients.is it true?
July 8, 2012 at 3:20 am
mah_j (7/7/2012)
so if the principal fails and we do a manual failover,i should do a forced service failover because the mirror database is now Disconnected and in Recovery mode.so we dont have unsent log any more?!it means that we loose the unsent log?
Only if you were mirroring async. If your mirroring was synchronous, at the point of failure all log records were committed on the mirror and hence you can just fail over manual or automatic (automatic with witness)
and what about second question ?if mirror cant send acknowledgement to principal,they wont be committed either in mirror and principal ?
because principal waits for acknowledgement from mirror and then commit and send to clients.is it true?
No. It would be a pretty stupid design if a high availability feature stopped both databases when one fails.
If the mirror goes offline, the principal carrys on working and switches the mirroring into disconnected mode.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2012 at 3:39 am
In a syncronous mirror environment with NO witness, if the mirror goes down or network connectivity between the two databases is lost, as Gail said, the log file continues to grow until it is either full (the t-log itself or the disk it resides on) as this VLFs cannot be marked for reuse until the data has been hardened on the mirror database.
If, while the mirror is down or the network connection is lost and the principal database goes down, you can't force a failover. And if you can, I wouldn't unless the business doesn't mind the possibility of losing the work that hadn't been transferred to the mirror database.
July 9, 2012 at 3:35 am
Gila and Lynn thanks a lot for your help.it was usefull.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply