Post reply

Execute sql without writing on transaction log

  • January 14, 2011 at 6:51 am

    #1273509

    GilaMonster (1/13/2011)

    sachnam (1/13/2011)

    1. will shrinking always shrink your log file even if it is in use?

    No, and repeatedly shrinking logs is not recommended.

    2. Why to run a full backup after the shrinking log process?

    Because too many people think that shrink log breaks the log chain. It does not. Hence no backup is required.

    Hmmm... maybe another blog post topic here...

    I agree that would be a good blog post Gail.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 14, 2011 at 7:38 am

    #1273545

    If you shrink a log, you are reclaiming inactive space (subject to a few constraints) for the OS by reducing the physical size of the log. However regrowing it can potentially create VLF issues.

    In terms of truncating, what you essentially do is throw away sections of the log for transactions that are committed and mark those VLFs are inactive. This does not affect the size of the log from the OS perspective, but it does mean that you cannot recover any of the work in this transaction log. You can't take a log backup, and your recovery time has just backed up.

    If I had

    full backup midnight

    log backup 8am

    truncate log at 4pm

    server fail 5pm

    I can only recover data to 8am. The truncate of the log has thrown away all transactions from the log since the last log backup

  • January 17, 2011 at 4:20 am

    #1274189

    Many thanks!

    sb

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply