August 11, 2011 at 4:02 am
My SQL 2005 database is mirrored but log is growing in size(37GB) at Principle server.
As database is mirrored I can't truncate/backup the log, what can be done to reduce the log size?
Thanks,
August 12, 2011 at 6:30 am
I don't advise shrinking it at all. The log space is growing because the database needs it.
If you're running out of hard drive space, add more as either a new drive or additional SAN space on the current drive. If you add another drive, add another tlog to the new drive.
EDIT: Is this the Primary or the Secondary server?
August 13, 2011 at 10:33 pm
Why can't u backup log and then shrink it?
August 14, 2011 at 10:00 am
santhubt (8/13/2011)
Why can't u backup log and then shrink it?
Because the database is mirrored - you cannot access the mirrored database.
If the log file is growing on the mirror, it must be growing on the principal database also. If the principal databases log file is not growing, but the mirrored databases log file is growing - then you have a problem committing the transactions on the mirror.
I have not seen that kind of issue before - so my thought is your transaction log on the principal is growing. Figure out why it is growing and correct that problem.
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
August 15, 2011 at 2:37 am
sorry for the confusion. Log is growing on Principle server.
Is it possible to backup log on Principle server? will that not break the Mirror?
Because I thought in high-safety method logs are internally backed up and copied over to Mirror database. And Backing up log will break the mirror setup?
August 15, 2011 at 3:18 am
There is nothing like that. You can go ahead and schedule log backup on principle. Your issue will be resolved.
Thanks and Regards
Santhu.
August 15, 2011 at 4:47 am
The principal database is in full recovery (has to be) as such you MUST back it up or the log will grow until it fills the drive. It's not optional.
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
August 15, 2011 at 6:09 am
Sqlsavy (8/15/2011)
sorry for the confusion. Log is growing on Principle server.Is it possible to backup log on Principle server? will that not break the Mirror?
Because I thought in high-safety method logs are internally backed up and copied over to Mirror database. And Backing up log will break the mirror setup?
Backing up the log on the Principal server will NOT break the mirror setup. As Gail said, you need to back it up or it will never truncate & reuse the space. If you don't back up your transaction log, your mirror will break the moment the log file completely fills up your hard drive.
August 16, 2011 at 6:59 am
Thank you all for the input.
I've setup log backups and my Mirror setup is running fine without any issues.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply