locked
Mirror issue RRS feed

  • Question

  • Hello

    I have setup mirroring and everything was fine until yesterday . Now we are facing issues with the log file of the database we are not able to shrink the file . Firstly i have issues with the mirror database and now the issue is with the principal database ,can anyone please help me out from tis problem .

    Regadrs
    Rithu

    Thursday, December 17, 2009 6:32 PM

Answers

  • Hi 

    Now is the problem with the mirror database or principal database ? When you shrink the log for the principal database then the changes will be automatically reflect at the mirror end ! if this is not happening please refer the following link . 

    http://support.microsoft.com/default.aspx?kbid=937531 : "The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005" . 

    What is the mirroring mode you are running at your environment ? 

    --------------------------------
    Thanks,Suhas V 
    Thursday, December 17, 2009 6:55 PM

  • Just shrink the logfile on the primary that gets changed in the mirror as well.

    Normally you wont be able to shrink a lot of space in a mirrored db , but you can at least get that reduced by looping the manual checkpoint command to reach a specified size for the logfile. - this is how i do thinbs on my mirror config.
    Thanks, Leks
    • Edited by LekssEditor Tuesday, December 22, 2009 5:49 PM
    • Proposed as answer by LekssEditor Tuesday, December 22, 2009 5:49 PM
    • Marked as answer by RithuVertha Wednesday, December 23, 2009 5:23 PM
    Friday, December 18, 2009 2:32 AM
    Answerer
  • Hi,

    An active long-running transaction can prevent transaction log cleanup and can potentially use up all available log space. To identify a long-running transaction, query the sys.dm_tran_database_transactions DMV in the principal database to find the longest running transaction and, if appropriate, kill it. For more information, please see http://technet.microsoft.com/en-us/library/ms366331(SQL.90).aspx.

    If it isn’t helpful, you could check the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view in the principal database to find the root cause of the issue. For more information about the values of these tow columns, please refer to http://technet.microsoft.com/en-us/library/ms345414(SQL.90).aspx.

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    • Marked as answer by RithuVertha Wednesday, December 23, 2009 5:23 PM
    Monday, December 21, 2009 7:45 AM

All replies

  • Hi 

    Now is the problem with the mirror database or principal database ? When you shrink the log for the principal database then the changes will be automatically reflect at the mirror end ! if this is not happening please refer the following link . 

    http://support.microsoft.com/default.aspx?kbid=937531 : "The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005" . 

    What is the mirroring mode you are running at your environment ? 

    --------------------------------
    Thanks,Suhas V 
    Thursday, December 17, 2009 6:55 PM

  • Just shrink the logfile on the primary that gets changed in the mirror as well.

    Normally you wont be able to shrink a lot of space in a mirrored db , but you can at least get that reduced by looping the manual checkpoint command to reach a specified size for the logfile. - this is how i do thinbs on my mirror config.
    Thanks, Leks
    • Edited by LekssEditor Tuesday, December 22, 2009 5:49 PM
    • Proposed as answer by LekssEditor Tuesday, December 22, 2009 5:49 PM
    • Marked as answer by RithuVertha Wednesday, December 23, 2009 5:23 PM
    Friday, December 18, 2009 2:32 AM
    Answerer
  • Please make a note of this scenario :

    If the mirroring is configured in high protection mode and if you suspend the database mirroring than you will not be able to shrink the log file of the prinicipal database . While the database mirroring seesion remains paused all the active records remain active and will be waiting for the priniciapal server to sebd the records to the mirror server . The unsent records accumulate at the prinicpal server and this will the log issue ! it is always recomonded to resume the paused session quickly .

    -----------------------
    Thanks,Suhas V

    Friday, December 18, 2009 11:30 AM
  • hi,what is the problem with mirroring? if you need to backup anything please do it on the principle server, the database on the mirror is in restoring state.  if you have problems with mirroring stop mirroring redo the backup, and the transaction backup and start mirroring again.

    -Vijay PMP, MCTS http://www.my-sql-share.blogspot.com Please click Mark as Answer if this had solved your issue
    Saturday, December 19, 2009 1:05 PM
  • Hi,

    An active long-running transaction can prevent transaction log cleanup and can potentially use up all available log space. To identify a long-running transaction, query the sys.dm_tran_database_transactions DMV in the principal database to find the longest running transaction and, if appropriate, kill it. For more information, please see http://technet.microsoft.com/en-us/library/ms366331(SQL.90).aspx.

    If it isn’t helpful, you could check the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view in the principal database to find the root cause of the issue. For more information about the values of these tow columns, please refer to http://technet.microsoft.com/en-us/library/ms345414(SQL.90).aspx.

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    • Marked as answer by RithuVertha Wednesday, December 23, 2009 5:23 PM
    Monday, December 21, 2009 7:45 AM