June 15, 2011 at 10:38 am
Due to a complicated set of circumstances I have a transaction log that is extremely large on my principal Server. The Mirror server has not caught up and appears to be falling farther behind. Tonight is my full backup, and the tlog will meet the expired backup date tonight as well. My question is what do I do about the mirror? My understanding of db mirroring is that it restores the transaction logs on the mirror server, and if this one very large log expires and is deleted before it has made it to the mirror server, will I have to rebuild the mirror, which will be a difficult situation due to the size of the database and the wan link involved.
Any suggestions?
June 15, 2011 at 3:09 pm
Well, first we need to deal with your misunderstanding.
Mirroring does not restore transaction log backups. Transactions are sent to the mirrored database and applied as fast as the system can send the data to the mirror location.
There are two modes of operation for mirroring. High Safety and High Performance. In High Safety mode, transactions from the principal are sent to the mirror - committed on the mirror and then committed on the principal. In High Performance, the transactions are committed on the principal and then sent to the mirror.
Now, on the principal system - the transactions in the transaction log will not be marked as reusable until a transaction log backup has been performed AND the transaction has been committed on the mirror. The VLF where those transactions are stored will not be marked as reusable until there are no open transactions that were started in the VLF file AND there are no active VLF's prior that have open transactions.
It appears that you have configured mirroring in high performance - and you have a very large lag between when the transactions were committed on the principal and when they are getting to the mirror.
Performing a transaction log backup will have no effect on what is being sent to the mirror. So, you don't need to worry about that - but, you do need to identify why the system is lagging so far behind and address that issue.
If it is the network causing the problems, you need to work with the network team. If it is the index rebuild processes - you need to restructure those, or live with the lag.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2011 at 3:45 pm
Jeffrey, thanks for the reply, After spending the day researching this, I now understand the difference. You are correct this is high performance mirror for DR purposes.
Based on the comment, "Now, on the principal system - the transactions in the transaction log will not be marked as reusable until a transaction log backup has been performed AND the transaction has been committed on the mirror."
I'd say that adds up fine, looking at the db mirroring monitor it seems to be hovering around 56gb for the unsent log. In fact it seems to be falling farther behind, I do see it send chunks of data though, but it never seems to stay constant. I know it's not using all the bandwidth it can because I can still move files back and forth over the wan link and they go very quickly.
June 15, 2011 at 6:30 pm
I think you need to figure out why you built up 56GB of transactions. Also, you need to work with your network team to figure out what is going on with the WAN connection.
I mirror several databases from California to Texas - and from Texas to California on the same WAN connection. After I perform index rebuilds, we fall behind and it can take several hours to catch up.
At one point, it was taking days to catch up - which was not acceptable. I was running the index rebuild job (smart re-indexer, only selecting indexes that need to be rebuilt/reorganized) on a weekly basis. That would generate in excess of 60-70GB of transactions which would take at least 2 days to transfer across.
To solve that problem, I moved the process back to a daily process to spread the impact. Now, the mirroring falls behind for several hours but is normally caught up before 6am. Occasionally, it will take longer depending on what indexes are selected.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply