21 responses

  1. Erik Bitemo
    April 14, 2009


    Nice writing. A short note on option #2 and #3: I wrote a sentence very similar to yours about using BACKUP LOG WITH NO_LOG and one of my readers explained that they have database mirroring, so they’re bound to FULL recovery model but they’re absolutely not interested in retaining the transaction log, so they truncate it regularly. Admittedly, this is not the average reason, but I’m getting used to the never-say-never feeling :) For #7 – I didn’t believe this can be thought seriously until I met a technical advisor who did it (and suggested their clients for a bunch of money).

  2. paul
    April 20, 2009

    Absolutely agree – but sometimes you just can’t persuade people *until* they have a disaster and find that something goes wrong with their primary HA technology.

  3. Pam
    April 20, 2009

    They should be interested in retaining the transaction log even with mirroring. I had a client with similar desires and I quickly showed them that it was a Really Bad Idea to not have the t-log backups. Mirroring is far from foolproof. What I wound up doing (as space was an issue) was to have a shorter cycle of t-logs preserved.

  4. meilleur site de jeux
    August 27, 2009

    There are many reasons why this is daft, including: 1) you have to shutdown to do it, so your workload is off-line 2) if the database wasn’t cleanly shut down, it won’t be able to be started again without using EMERGENCY mode repair, and your data will be transactionally inconsistent 3) as the log can’t be instant initialized, the database won’t come online until the log has been created and zero’d. Just don’t do this.

  5. Steve Syfuhs
    September 8, 2009

    Setting up the production server tomorrow including backup. First test? Yank the power and verify log backups worked. That way there *is* a disaster.

    • Mark
      June 11, 2013

      Hi Paul, I have always been taught to have only one log file, but was wondering about the strategy of having two log files, a “primary” one that is carefully sized through capacity planning and has autogrowth shut off, and a “secondary” log file that would hopefully never be reached but that can be used and autogrow in case the first one fills up due to unforeseen multiple open transactions? (I read about a DBA using this strategy on a blog, sorry don’t have the details anymore.)

      What would be the negative consequences of implementing such a strategy? I am not experienced enough to know whether this could be effective but it seemed like a cool idea, however due to the general consensus against multiple logfiles I am hesitant to accept this unusual but interesting strategy.

      • Paul Randal
        June 16, 2013

        No perf issues, but for disaster recovery, the more log space there is, the longer it takes to zero initialize it when restoring from scratch.

  6. Danny
    October 2, 2013

    What I don’t understand why in my case the ldf file is still growing whilst the recovery model is Full and every hour backup taken of the ldf file.
    Checked active transaction with DBCC OPENTRAN and result was : No active open transactions.
    Any suggestions?

    • Paul Randal
      October 2, 2013

      What’s the result of “SELECT log_reuse_wait_desc FROM sys.databases” for your database?

  7. Jaime Martinez
    November 12, 2013

    Hello Paul,

    I am reading a lot lately about recovery models, log backups and so on, including your great posts. But I still have some unanswered questions in my mind. Recently I came across a sceneario where due to SQL’s AAG, the recovery model of the databases was set to full. No transaction log backup was being done, but full backups happened every night.

    1. Why does the transaction log keep growing uncontrolled?
    2. In case of failure wouldn’t be enough to have the full backup and the log just being filled from that point of time?
    3. If full backups inlcude log file backup (is that right?), why does this not help keeping the transaction log small?

    Thanks a lot!


    • Paul Randal
      November 12, 2013

      1) Because you’re not performing transaction log backups
      2) No
      3) No. Full backups only include enough log necessary to recover the full backup when restored.

      You must perform log backups when in the full recovery mode otherwise your log will grow forever.

      See http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-7-of-30-backups-recovery-models-and-backup-types/

      • Fernando Paez Becker
        July 14, 2014

        Hi Paul! I’ve a situation related to log. In a database that is not involved in any replication schema (and never was) after a backup log a got the message “SQL Server Log File Won’t Shrink due cause “log are pending replication” “…. First all a have to mention that the database is in simple mode (sql version 10.0.5500), after an alert i got noticed that log file was growing and growing… So… a got full database backup, put database in full mode and take backup log (and then i got initial message posted)… the log_reuse_wait_desc field of sys.databases for database shows “replication”… the workaround was (after try an sp_repldone which with sense shows “The database is not published. Execute the procedure in a database that is published for replication.” , also select DATABASEPROPERTY(‘databasename’,’ISPublished’) shoes an expected Zero) to execute sp_removedbreplication…

        I didn’t found a explicit microsoft bug o case opened for this situation… Do you now what could be the root cause for my situation in order to prevent it if is possible.. .Thanks a lot in advance, and congratulations for all your posts.

      • Paul Randal
        July 15, 2014

        No idea what could have caused that, but I’ve heard of it before. You’ll need to run sp_removedbreplication in that database.

Leave a Reply




Back to top
mobile desktop