September 16, 2013 at 9:08 am
Hi All
I have mirrored database with 2 Databases Motor and one witness. Size of one Database is 467 Mb, but transaction log is 64 GB. Model of backup - FULL
How to shrink this transaction log. Below is my proposal, but Im not a specialist (Is's correct ?):
1. Backup DB and Transaction Log
2. Remove mirroring from this DB
3. Shrink Transaction Log (but I don't know what happend, if I have set backup model to FULL) (change backup model to Simple ?)
4. Turn on mirroring
5. Set new policy in Maintanace Plan to backup Transaction Logs
thx for any help
September 16, 2013 at 10:16 am
Just shrink it. None of the other steps are necessary (though you probably should take a full backup first). Make sure you've got your log maintenance implemented correctly.
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
September 16, 2013 at 6:31 pm
kalczynski (9/16/2013)
Hi AllI have mirrored database with 2 Databases Motor and one witness. Size of one Database is 467 Mb, but transaction log is 64 GB. Model of backup - FULL
How to shrink this transaction log. Below is my proposal, but Im not a specialist (Is's correct ?):
1. Backup DB and Transaction Log
2. Remove mirroring from this DB
3. Shrink Transaction Log (but I don't know what happend, if I have set backup model to FULL) (change backup model to Simple ?)
4. Turn on mirroring
5. Set new policy in Maintanace Plan to backup Transaction Logs
thx for any help
Is the mirroring synchronized?, if so, just take a manual log backup and shrink it? Why is the log that big? when was the last time you did a log backup?.....you might want to do log backups more often to avoid this again.
Regards,
SQLisAwe5oMe.
September 16, 2013 at 11:57 pm
HI All
Thank You for response. My database operates in synchronous mode. I have two servers working in mirroring mode a thirty server witness. This is MS SQL 2008 R2, The database is in FULL Recovery Model.
Reffering to backup transaction log. The true is I forgot to implement backup transaction logs in my mainanace plans.
The last question is, Can I make shrink transaction log, when database is in mirroring mode ? It's correct ? If Yes then I Use graphical interface (task->shrink file->...) or script:
use MirroredDb;
go
dbcc shrinkfile('MirroredDb_log', 0);
go
http://msdn.microsoft.com/en-us/library/ms189493.aspx
After when I shrink this file, I planing to implement new mainanace plan: daily backup transaction log - every 15 minutes and weekly.
Regards
Arek
September 17, 2013 at 3:15 am
kalczynski (9/16/2013)
Can I make shrink transaction log, when database is in mirroring mode ?
Yes.
Don't shrink to 0. Shrink the log to a sensible size based on activity.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply