locked
Tran log shrink on mirror RRS feed

  • Question

  • Hello,

    Walked into situation where transaction logs for mirrored databases are significantly larger (many GBs) than transaction logs on principals.  Will Dbcc shrinkfile shrink both principal and mirror or just principal? 
    TIA,
    Tuesday, February 17, 2009 4:05 PM

Answers

  • Hi Joe,

    Unfortunately a DBCC SHRINKFILE will only have an effect on the pricincipal server to decrease the size of the transaction log. You will need to failover to the mirrored database (or break the mirror) to decrease the size of the log on the mirror database.


    Cheers,
    Ulrike - MSFT

    This posting is provided "AS IS" with no warranties, and confers no rights.”

    • Marked as answer by Joe_Hell Friday, June 19, 2009 7:07 PM
    Friday, February 20, 2009 9:27 AM

All replies

  • Hi Joe,

    Unfortunately a DBCC SHRINKFILE will only have an effect on the pricincipal server to decrease the size of the transaction log. You will need to failover to the mirrored database (or break the mirror) to decrease the size of the log on the mirror database.


    Cheers,
    Ulrike - MSFT

    This posting is provided "AS IS" with no warranties, and confers no rights.”

    • Marked as answer by Joe_Hell Friday, June 19, 2009 7:07 PM
    Friday, February 20, 2009 9:27 AM
  • Try to execute this command it will truncate the T log and shrink the log file size need to execute

    Simultaneously dont forget to use the Database name.

     

    backup log 'yourdatabasename' to disk ='D:\YourdatabaseName.trn'

    go

    dbcc shrinkfile (yourdatabase_logfilename,1)

     

    For database log file name try to execute sp_helpdb 'Yourdatabasename'


    BR
    Praveen Barath

    Marked as answer if it helps.
    http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/4d4ee0f6-d70d-41b2-b972-f521d27f2441/


    Thursday, March 12, 2009 4:37 PM
    Answerer
  • Hi ,

    Schedule a transaction log backup in principal server on  daily basis( transaction log backup can performed frquently depending on size ). This will automatically reduce the log file size and same changes will take effect in Mirror server also .

    No need to perform dbcc shinkfile - this worked out well in critical PROD server
    • Proposed as answer by Rajganesh.dba Saturday, May 9, 2009 11:47 AM
    • Unproposed as answer by Joe_Hell Friday, June 19, 2009 7:03 PM
    Saturday, March 21, 2009 6:32 AM
  • Thank You Urlike,
    we did the shrink when we failed over. 
    Thank you for your CORRECT answer.
    THe other supposed responses show a marked lack of understanding of SQL server and
    a warning to all that be very carefull in implementing supposed solutions without testing.

    Friday, June 19, 2009 7:10 PM
  • DBCC SHRINKFILE on principal doesn't work.  It gives following message -

    Cannot shrink Log file because the logical log file located at the end of the file is in use.

    Tuesday, October 26, 2010 10:49 PM
  • To shrink transaction file for a database with mirror, you must backup as there are actives Virtual Log File :

    http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499.aspx

     

    Monday, March 14, 2011 10:01 AM