locked
Transaction Log settings in mirrored database RRS feed

  • General discussion

  • Hello,

    my transaction log got filled full recently stopping all transactions on the server, I have truncated the log backing it up and set "auto shrink"

    on principle db. Do I need to do the same on mirror? or maybe "auto grow" unrestricted grow option is better?  in my SQL server 2008 I see option for enable for transaction log shipping, is this good to enable?

    thanks

    Saturday, December 18, 2010 8:28 AM

All replies

  • It's probably not a great idea to have auto shrink turned on, you need to size your files, data and log, according to your databases need. You also need to manage the log file, on the principal database to manage growth. As your using database mirroring this means your database is in full recovery mode and as Such SQL Server is expecting *you* to manage the log by taking regular transaction log backups. Regular log backups will allow SQL Server to re-use inactive portions of the log and hence your log file won't grow uncontrollably. Your question about log shipping. Database mirroring is a slightly different tool from log shipping. I believe Mirroring was built from Log Shipping but the two are not the same. any given database can only have one mirror, but a database can be logged shipped to several different servers. Whether you need to enable it or not really depends on your businesses DR strategy. Hope this Helps
    Gethyn Ellis blog: http://www.gethynellis.com/
    Saturday, December 18, 2010 11:44 AM
  • thanks for tips. I wonder why auto shrink is not safe only in mirroring or in any setup?

    So I guess I'll set unrestricted grow and automatic regular db log back up which should help manage the log.

    thanks again

    Saturday, December 18, 2010 12:06 PM
  • Hi kc2ine,

    Autoshrink is a bad idea in anny situation.
    Read http://www.karaszi.com/SQLServer/info_dont_shrink.asp


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Saturday, December 18, 2010 12:37 PM
  • Hi Kc2ine,

    Auto-shrink is not good and its not at all a good practice in a typical SQL server production database as it shrinks the data file of your which inturn causes fragmentation of your indexes there by reducing the performance .  Google PAul Randal + autoshrink and you will have so many blog articles that explains each and every aspect of data file shrink issues.

    Now coming back to your question with the HA or DR solution - with respect to mirroring log file size management is bit of an important thing that your DBA should be cautious about , thats primarily because you are mandated to run your databases in FULL recovery model. And shrinking on the log file can be done under certain circumstances where your log file disk is running out of space also make sure that you have a better understanding on how a shrink works differently on a log file as compared to the data file.


    Thanks, Leks
    Saturday, December 18, 2010 11:35 PM
    Answerer
  • I read that transactional log can  be not truncated or truncation be delayed for many reasons especially in mirrored db.

    So my question: is it good to set unrestricted grow as safety net when truncation not happen?

    The problem I have is that server is in remote location with no supervision and I want to have it as much automated as possible.

    thanks

    Wednesday, December 22, 2010 9:18 AM
  • A frequent transaction-log backup can help in this situation. With the t-log backup in place, all the committed transactions are removed from the transaction-log, making space available for the newer transactions. This will not reduce the current log file size, however, it can help in a low rate of growth for the log file. However, if you have a lot of active transactions and your t-log size is full, then it will grow.

    A more better way to look at it, why you should not shrink the log file? Adding to the experts, if you have a one time bulk-load kind of operation, then you probably want to reduce the log space, however, if you have an OLTP box, then it doesn't help much. You can reduce the file size by shrinking it, SQL is going to increase it when required, causing a loss of resources. As you might know, Shrinking is very resource intensive, you probably want to re-think before you shrink any file.

    Hope, I didn't confuse you. :)


    SKG: Please Marked as Answered, if it resolves your issue.
    Wednesday, December 22, 2010 9:34 AM
  • well, so there is no straight answer to the subject of managing logs and space I guess.

    I Understand all depends on situation.  I read also  somewhere that there is some bug

    which prevents to truncate t-log during full db backup. I may review my scripts for db backup.

    Wednesday, December 22, 2010 9:47 AM
  • Truncate of T-log during full backup happened only if the database is in Simple recovery model, which is not supported for DB mirroring. As mentioned earlier, if you find your log is growing, it means it need space to complete all the transactions which is nt available. So either the log bakup is not in-place / not-frequent which requires to clean up the inactive portion of your t-log. The best possible way may be: Frequent t-log backup & a big space for the t-log file growth, for an OLTP system using Database Mirroring feature.
    SKG: Please Marked as Answered, if it resolves your issue.
    Wednesday, December 22, 2010 10:11 AM
  • Hi kc2ine,

    I read also  somewhere that there is some bug

    which prevents to truncate t-log during full db backup.

    That's not a bug; it's by design.
    While the full backup is running, data may change. In order to bring a
    database to a consistent state after restoring a full backup, part of
    the transaction log is added to the full backup. This is not a backup
    of the complete transaction log, but only the section that is required
    to roll forward or roll back all transactions that were active during
    the full backup.
    And as with all other processes that need a part of the log, the full
    backup also prevents clearing out the portion of the log it still
    needs.

    To your other question - on an unattended server, I would definitely
    enable unlimited log growth as a safety net. But you should also try
    to prevent that safety net from ever being needed, so size your log
    appropriately, never allow it to auto-shrink, and only use manual
    shrink if some exceptional circumstance has caused it to bloat.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, December 22, 2010 10:45 AM

  • Truncate of T-log during full backup happened only if the database is in Simple recovery model, which is not supported for DB mirroring. As mentioned earlier, if you find your log is growing, it means it need space to complete all the transactions which is nt available. So either the log bakup is not in-place / not-frequent which requires to clean up the inactive portion of your t-log. The best possible way may be: Frequent t-log backup & a big space for the t-log file growth, for an OLTP system using Database Mirroring feature.
    SKG: Please Marked as Answered, if it resolves your issue.

    thanks but t-log backups do not recover space don't they? and shrinking is not good idea so the solution is to have a big drives

    and unrestricted grow settings.

    Wednesday, December 22, 2010 11:24 AM
  • Yes, that's the option. :)
    SKG: Please Marked as Answered, if it resolves your issue.
    Wednesday, December 22, 2010 11:26 AM
  • thanks guys.

    p.s.

    I would mark this question as solved but don't see where is the button :)

    Wednesday, December 22, 2010 12:28 PM
  • >I would mark this question as solved but don't see where is the button :)

    You go to the reply (or replies) that contain the information that
    answered your question, and then click the "Mark as Answer" button.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, December 22, 2010 1:21 PM
  • p.s.

    I would mark this question as solved but don't see where is the button :)

    I think you need to change it from discussion to Question, to enable the Mark as Answer button :)

    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, January 11, 2011 9:40 AM