Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Truncate log for Mirrored Database


Truncate log for Mirrored Database

Author
Message
rishgup
rishgup
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 75
Thanks to all who give suggesstions. Now my database is all set.

I set up transaction log backup every 1 hour and its working fine. And it is mirrored as well. Thanks once again to all.
BobAtDBS
BobAtDBS
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 356
Just to add in some details - No need to break mirroring, do anything special on the mirror.

If you simply use DBCC ShrinkFile on the principle's log file, the resulting change will be applied to the mirror as well in a very small transaction. The mirror size will normally stay perfectly in synch with the principle's size.

We ran out of disk space on the mirror, so mirroring was suspended and the principle's log grew rather large. After clearing room on the mirror's log drive, resuming mirroring, letting it catch up, and finally letting the next principle log backup take place, we were able to use DBCC ShrinkFile on the principle's log file and the change was instantly reflected on the mirror.

SQL Server 2005, SP2 and SP3 have worked the same.

By the way, we don't go around shrinking transaction logs all the time, but this one had grown to 146 Gig, when it is normally about 15 Gig. Yes, it's a fairly large database.


Student of SQL and Golf, Master of Neither
kadir.evciler
kadir.evciler
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 56
Hi Rishabh ,

When you backup transaction log a database that is pricipal, transaction log doesn't decrease but precentage usage of transaction log decreases. Execute dbcc sqlperf(logspace) before backup transaction log and see Log Space Used(%) value and re execute after backup . You will see Log Space Used(%) decreased
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47213 Visits: 44369
Please note: year old thread.


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


sacha.bessard
sacha.bessard
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 92
It's possible : to shrink transaction file, backup must be performed as there are actives Virtual Log File :
http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499.aspx
sacha.bessard
sacha.bessard
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 92
It's possible : to shrink transaction file, backup must be performed as there are actives Virtual Log File :
http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499.aspx
mahesh.shinde
mahesh.shinde
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 129
You can follow below steps

1. Run below script against db in mirroring on principle server.

DECLARE @DatabaseName VARCHAR(50);
SET @DatabaseName = 'Mirrored DB Name' --> Replace the mirror db name here.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = @DatabaseName

2. If output column log_reuse_wait_desc is saying LOG_BACKUP then simply take multiple time log backup of mirror database. Mostly 3 continuous T-log backup would enable db to shrink the log file with following command.
DBCC Shrinkfile(2,1024)

Above steps would fix the issue.

Thanks,
Mahesh Shinde
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47213 Visits: 44369
mahesh.shinde (5/26/2013)
2. If output column log_reuse_wait_desc is saying LOG_BACKUP then simply take multiple time log backup of mirror database. Mostly 3 continuous T-log backup would enable db to shrink the log file with following command.
DBCC Shrinkfile(2,1024)


You can't take log backups of a mirror database, the database is in the restoring state. Nor can you shrink the log of a mirror database (plus shrinking to 1MB is a poor recommendation).

Log backups and shrinks can only be done on the principal database of a mirroring configuration.

p.s. 5 year old thread.


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


mahesh.shinde
mahesh.shinde
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 129
Yes database which is residing on principle server. You've to take T-log backup multiple time to enable database for shrinking T-log file.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47213 Visits: 44369
mahesh.shinde (5/26/2013)
Yes database which is residing on principle server. You've to take T-log backup multiple time to enable database for shrinking T-log file.


That's the principal database, not the mirror. You cannot take log backups of the mirror, only of the principal, and if you're got a proper log backup strategy you will not need to take multiple log backups. Shrinking the log is something that should be done with caution and never just a blanket shrink to 1MB without any thought.

p.s. 5 year old thread with the OP's problem solved 5 years ago.


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