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 ««123»»

Truncate log for Mirrored Database Expand / Collapse
Author
Message
Posted Friday, October 24, 2008 9:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 23, 2009 8:19 AM
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.
Post #591320
Posted Tuesday, September 15, 2009 8:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 298, Visits: 335
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
Post #788231
Posted Monday, November 29, 2010 4:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 20, 2012 6:33 AM
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
Post #1027221
Posted Monday, November 29, 2010 5:32 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: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
Please note: year old thread.


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 #1027250
Posted Monday, March 14, 2011 3:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 25, 2011 2:59 AM
Points: 8, Visits: 55
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
Post #1077609
Posted Monday, March 14, 2011 3:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 25, 2011 2:59 AM
Points: 8, Visits: 55
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
Post #1077610
Posted Sunday, May 26, 2013 5:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 4:33 AM
Points: 5, Visits: 93
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
Post #1456857
Posted Sunday, May 26, 2013 7:31 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: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 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 #1456861
Posted Sunday, May 26, 2013 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 4:33 AM
Points: 5, Visits: 93
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.
Post #1456863
Posted Sunday, May 26, 2013 7:41 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: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 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 #1456865
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse