locked
Mirroring RRS feed

  • Question

  • Hi,

    If log file is full in Mirrored database then what to do?

    Thursday, August 8, 2013 11:57 AM

Answers

  • I am just putting my thoughts....

    1) According to Surya "Yes it's fuller than on the primary.." might be its due log file is restrictiction is not sync with primary due to disk capacity difference from Primary and mirrored.

    2) Agree with not through log content mentioned take log backup.

    3) Log is full at mirrored server not primary server according to Surya so there is no issue to keep going transaction on primary if mirroring is configured async , and in async mirroring unsent log keep growing as primary server if it not transferred at mirrored server.

    4) And if log is full at secondary and not able to accept any transaction forwarded by primary then how it is possible to accept transaction of backup log at secondary.

    Take a try with backup the log on the primary , the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.

    Same is discussed at http://stackoverflow.com/questions/1033158/shrinking-the-transaction-log-of-a-mirrored-sql-server-2005-database 

    Agree with Shanky that breaking mirroring is only not the option to fix this issue, If failover is working then make secondary as primary and take log backup and again take fail over to make it in original.


    Saturday, August 10, 2013 6:02 PM
  • Hello Surya,

    I suppose transaction log file of DB on primary is full and has it ha no place to increase.The thing with transaction  log is that it can be managed perfectly by taking frequent trnsaction log backup it has 2 advantages

    1. Log size will not grow huge and as soon as trn backup happens truncation will occur and inactive part of logs will be truncated

    2.Since log sizw will be small amount of log going from primary to sec will be small and so less load on server as well as network

    If UAT ,then you can think forcefull trncating log by runnig .But this should be your last action if all other actions fail

    backup log db_name to disk='null'

    Immediate action should be to take transaction log backup and try to shrink logs.Also remember shrinking causes fragmentation so this should be rare activity

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by PrinceLucifer Thursday, August 8, 2013 7:49 PM
    • Marked as answer by Fanny Liu Thursday, August 15, 2013 11:19 AM
    Thursday, August 8, 2013 12:04 PM

All replies

  • Hello Surya,

    I suppose transaction log file of DB on primary is full and has it ha no place to increase.The thing with transaction  log is that it can be managed perfectly by taking frequent trnsaction log backup it has 2 advantages

    1. Log size will not grow huge and as soon as trn backup happens truncation will occur and inactive part of logs will be truncated

    2.Since log sizw will be small amount of log going from primary to sec will be small and so less load on server as well as network

    If UAT ,then you can think forcefull trncating log by runnig .But this should be your last action if all other actions fail

    backup log db_name to disk='null'

    Immediate action should be to take transaction log backup and try to shrink logs.Also remember shrinking causes fragmentation so this should be rare activity

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by PrinceLucifer Thursday, August 8, 2013 7:49 PM
    • Marked as answer by Fanny Liu Thursday, August 15, 2013 11:19 AM
    Thursday, August 8, 2013 12:04 PM
  • I totally agree with shanky on the assumption. I would still take a look at sys.databases though... There is a field in there called "log_reuse_wait_desc", that will immediately tell you why your log doesn't get overwritten...
    Thursday, August 8, 2013 7:49 PM
  • Hi,

    If log file is full in Mirrored database then what to do?


    You get a Log Backup Job in place and running (!)

    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Thursday, August 8, 2013 7:54 PM
  • Hi Shanky,

    If log file is full in mirror server then..  ?

    Saturday, August 10, 2013 10:26 AM
  • If log file is full in mirror server then..  ?

    Why do you think, it's full?

    is it "fuller" than on the primary???


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Saturday, August 10, 2013 11:08 AM
  • Hi Andreas,

    Yes it's fuller than on the primary..

    Saturday, August 10, 2013 11:12 AM
  • Yes it's fuller than on the primary..

    interesting

    and about how much are we talking here?

    Can you specify the size and percentage/MB used of all files involved (primary, mirror)?


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Saturday, August 10, 2013 11:20 AM
  • Hi,

    In principle server max logfile size is 3 gb and in mirror server max logfile size is 2 gb.

    Saturday, August 10, 2013 1:16 PM
  • In principle server max logfile size is 3 gb and in mirror server max logfile size is 2 gb.

    I am a bit confused. Maybe my memory falses me here, but afaik this cannot be since the mirror should be a copy of the primary. Almost to the last bye except for in flight transactions. - Anyone else?

    Can you explain how you set up mirroring, which steps you took, especially regarding transaction log?


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Saturday, August 10, 2013 1:41 PM
  • Mirroring is based on transcation on primary.So if log is full at mirrored server transcation can't be transfer at mirrored server.

    So as per my understanding you can't shrink a t-log if the database is mirrored unless you off the mirroring.

    I am bit suspected that  below command will work.

    backup log db_name to disk='null'

    one more thing i would suugest that if log is full that  the amount of active log space will grow. In this case, you may need to stop mirroring,

    take a log backup that truncates the log, apply that log backup to the mirror database and restart mirroring,

    And apply the Shanky suggestion taking frequent trnsaction log backup.

    Saturday, August 10, 2013 2:19 PM
  • 1) Mirroring is based on transcation on primary.So if log is full at mirrored server transcation can't be transfer at mirrored server.
    So as per my understanding you can't shrink a t-log if the database is mirrored unless you off the mirroring.

    2) I am bit suspected that  below command will work.

    backup log db_name to disk='null'

    3) one more thing i would suugest that if log is full that  the amount of active log space will grow. In this case, you may need to stop mirroring,

    4) take a log backup that truncates the log, apply that log backup to the mirror database and restart mirroring,

    1) The log should be the same in first place.

    2) Please do not throw away log contents, espacially not before knowing what's really going on.

    3) I do not understand what this means. If it would grow, it would not be "full". And why stopping it then?

    4) all log backups allow log truncation, why restart mirroring? That might be a last resort after all.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com


    Saturday, August 10, 2013 2:26 PM
  • In principle server max logfile size is 3 gb and in mirror server max logfile size is 2 gb.

    now I gotta ask a stupid question, forgive me, but I want to make sure:

    The mirroring session is actually alive/running?

    What does this view return?

    SELECT * FROM sys.database_mirroring
    WHERE database_id = DB_ID('<Your_db_Name>')


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Saturday, August 10, 2013 2:36 PM
  • In principle server max logfile size is 3 gb and in mirror server max logfile size is 2 gb.

    now I gotta ask a stupid question, forgive me, but I want to make sure:

    The mirroring session is actually alive/running?

    What does this view return?

    SELECT * FROM sys.database_mirroring
    WHERE database_id = DB_ID('<Your_db_Name>')


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    I agree with Andreas here, Surya are you asking this question because it was asked you in an interview /you are learning mirroring or really are you facing this problem.Please I would like you to respond to query provided by Andreas,

    Pradeep,

    Below query will work on mirroring ,but as advised if you are using UAT or playing with Mirroring you can use it.Also if trn log is full I dont think breaking mirroring is only option

    backup log db_name to disk='null'


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, August 10, 2013 2:43 PM
  • I am just putting my thoughts....

    1) According to Surya "Yes it's fuller than on the primary.." might be its due log file is restrictiction is not sync with primary due to disk capacity difference from Primary and mirrored.

    2) Agree with not through log content mentioned take log backup.

    3) Log is full at mirrored server not primary server according to Surya so there is no issue to keep going transaction on primary if mirroring is configured async , and in async mirroring unsent log keep growing as primary server if it not transferred at mirrored server.

    4) And if log is full at secondary and not able to accept any transaction forwarded by primary then how it is possible to accept transaction of backup log at secondary.

    Take a try with backup the log on the primary , the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.

    Same is discussed at http://stackoverflow.com/questions/1033158/shrinking-the-transaction-log-of-a-mirrored-sql-server-2005-database 

    Agree with Shanky that breaking mirroring is only not the option to fix this issue, If failover is working then make secondary as primary and take log backup and again take fail over to make it in original.


    Saturday, August 10, 2013 6:02 PM