August 24, 2009 at 8:27 am
Hi,
I have a database with Mirroring in a pair of SQL Server 2005 with SP2. The mirror is configured in High Availability and FULL Transaction Safety.
My problem is that the size of the Log file is near to 80 GB and I have troubles with the capacitiy of the Hard Disk.
I would like to know if, in that mode of mirroring, it's right to truncate this file, and the better way to do it at regular intervals.
Thanks in advance.
August 24, 2009 at 8:41 am
That's an interesting question. I would think the truncate would get passed through to the mirror as well.
Is the log growing? How often are you doing log backups? The best way to manage a smaller log size is to make more frequent log backups.
August 24, 2009 at 8:55 am
Have a look in the Database Mirroring Monitor at the 'Unsent log', 'Time to send log' 'Time to restore log' values. If they are increasing then your network is struggling to cope with the amount of new transactions being generated.
There is a known performance issue with Database Mirroring that is allegedly fixed in SP3.
You could try switching the mirroring mode to asynchronous (High Performance), but obviously there is more of data loss risk.
August 24, 2009 at 10:30 am
Well, I'm not doing backups of the logs, only of the data. But if I'd do this backups, would the size of the log file be reduced automatically? or I must do another thing?
Thanks
August 24, 2009 at 11:01 am
If you perform log backups, the log space is reused. Whatever is backed up is marked as such, and that space is reused. If you don't do log backups (and you are in full or bulk logged mode) the log file increases until you run out of space.
August 25, 2009 at 12:29 pm
A) You should be running transaction log backups for mirrored dbs if they are using that sort of recovery model
B) I believe if you shrink the transaction log on the principal the mirror transaction log will also be shrunk, but don't take my word on it, you should test this to be sure.
August 25, 2009 at 1:20 pm
To reiterate what Steve said, you will be able to re-use the log space when doing log backups. In full recovery mode, you should be performing t-log backups at a regular interval. The interval may be determined by number of transactions, hardware being used (tape or hard drive or other).
If you truncate the t-log, that action should be forced across the mirror. From past experience and testing, this is what happened for us.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 25, 2009 at 2:19 pm
I can confirm for you that when a log file on the principal shrinks in size, the one on the mirror does as well.
The Redneck DBA
August 25, 2009 at 2:42 pm
When using DB mirroring in the past, I've set the transaction log to backup every hour during business hours and every 3 hours during off-hours. This helped keep the log file under control.
You will need to see what schedule best fits your environment. You will need to account for maintenance plans since they cause the log file to grow large very quickly.
August 27, 2009 at 4:40 pm
Hi,
I had the same problem and this is how I fixed it. First time when I spotted that my log was big ( around 60 GB).
1) I broke the mirror and truncated and reduced the log size.
2) Re-established the mirror.
3) Scheduled a log backup daily, since mirror is there I don't need the log backup so I delete the backup file as soon as it's created.
--Never had a problem since.
August 27, 2009 at 8:25 pm
vinaydiwakar (8/27/2009)
3) Scheduled a log backup daily, since mirror is there I don't need the log backup so I delete the backup file as soon as it's created.
Not knowing your DR tolerances and requirements, I can't say that it's wrong for you to delete the log backup, but it's important to note that if you delete the log backup, you will only be able to restore back to the last full (or diff) backup. You do have the mirror there, but this won't protect you from the situation where a user truncates a table by accident. If this were to happen an hour before your full backup, both your principal and mirror will lose the entire table, and you will have lost 23 hours of data.
Even if you take the log backup immediately after your full backup, if you delete the log backup, you've broken the log chain, and can only restore back to the full backup. For this reason, I highly recommend leaving log backups for at least a day. Longer is always better, in case your full backup fails or gets corrupted and you have to use an older backup.
August 28, 2009 at 1:21 am
Thanks to all, guys.
August 28, 2009 at 6:03 am
Jim McLeod
Not knowing your DR tolerances and requirements
..EMC with BCV 3 times daily..as per the SLA. In case of an accident we rollback 8 hours earlier...from BCV volume....so, don't need the backup.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply