Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mirroring and big transaction log file Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 2:54 AM
Points: 2, Visits: 8
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
Post #1495145
Posted Monday, September 16, 2013 10:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1495179
Posted Monday, September 16, 2013 6:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:02 PM
Points: 422, Visits: 2,662
kalczynski (9/16/2013)
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


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.
Post #1495336
Posted Monday, September 16, 2013 11:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 2:54 AM
Points: 2, Visits: 8
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


Post #1495375
Posted Tuesday, September 17, 2013 3:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1495410
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse