SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring and big transaction log file


Mirroring and big transaction log file

Author
Message
kalczynski
kalczynski
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90757 Visits: 45284
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

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


SQLisAwE5OmE
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 3068
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.
kalczynski
kalczynski
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90757 Visits: 45284
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search