Shrinking Transaction Log File "the right way"

  • DiStas

    SSC Enthusiast

    Points: 147

    Comments posted to this topic are about the item Shrinking Transaction Log File "the right way"

  • Perry Whittle

    SSC Guru

    Points: 233794

    Your article mentions

    distas


    Here is important point in regards to DBCC SHRINKFILE not to be misinterpreted: DBCC SHRINKFILE (<Transaction_LogFile_LogicalName>, TRUNCATEONLY) does truncate a transaction log file, i.e. breaks the LSN - Log Sequence Number. This will result in inability to restore database up to the point of failure, if the full or differential database backup is not taken right after and database ...oops... fails after this transaction log file truncation operation.

    But I think you have misinterpreted, this command (which is no longer available in SQL Server) will truncate the log and break LSNs

    BACKUP LOG ... WITH NO_LOG or TRUNCATE_ONLY

    The options TRUNCATEONLY and NOTRUNCATE for DBCC SHRINKFILE do not apply to transaction log files, they are applicable to data files only.

    This link details the following

    Books Online


    NOTRUNCATE

    Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink.

    NOTRUNCATE is applicable only to data files. The log files are not affected.

    TRUNCATEONLY

    Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

    target_size is ignored if specified with TRUNCATEONLY.

    TRUNCATEONLY is applicable only to data files.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Here is important point in regards to DBCC SHRINKFILE not to be misinterpreted: DBCC SHRINKFILE (<Transaction_LogFile_LogicalName>, TRUNCATEONLY) does truncate a transaction log file, i.e. breaks the LSN - Log Sequence Number.

    No , it does not. Shrinking never breaks the log chain. That 'truncateonly' means to only release free space at the end of the file not to shuffle data pages around (emphasis data pages, it's ignored when shrinking a log).

    The only things that break the log chain are:

    - Switch to simple recovery

    - Backup log with truncate only (SQL 2005)

    - Reverting from a database snapshot

    - Rebuilding the log

    What you have stated there is a prevalent and very irritating myth. If you'd tested it, you'd have seen that the shrink does not break the log chain.

    Instead, backup the transaction log file: (SQL Server 2008 Standard Edition, Enterprise Edition / SQL Server 2008R2 any addition)

    BACKUP LOG <DatabaseName> TO DISK = N'<drive>:\<backup_folder>\<database_name>LogBackup.trn' WITH NOFORMAT, INIT, NAME = N'<DatabaseName> - Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, COPY_ONLY, STATS = 1;

    A copy only log backup won't free any space in the log, so I don't known what the point of running this is.

    Also might like to mention that statement is Enterprise only, because of the compression.

    then execute

    DBCC SHRINKFILE(<Transaction Log File Name (logical)>, 1)

    which will shrink the T-Log to to its last used extent.

    Log files don't have extents. It'll shrink by removing any inactive VLFs that are at the end of the file. If the last VLF in the file is part of the active portion of the log, it won't shrink at all.

    Shrinking as small as possible is a horrid thing to do, unless you intend to immediately grow the log file again. If the idea is just to shrink the log after unusual growth, then it should just be shrunk to normal size. See Managing Transaction Logs[/url]

    Warning: Backing up the transaction log file prior to its shrinkage is required because in the total majority of situations, the T-Log file won't shrink because of active segments either at its tail or towards it. Those ones are "freed-up" exactly by running transaction log file backup

    Except that you said to run the log backup with copy_only, which won't free anything. Even a normal log backup won't free VLFs that are active and at the end of the file if they're needed for something else (transaction, replication, mirroring, etc). Shrink cannot move log records around the log.

    In this kind of situation, when you're getting 9002 errors and database is in "Suspect" state, there is really no time to wait for transaction log file backup completion before you can proceed with its shrinking,

    A full log will never send the database suspect (if it ever does, you've hit a critical bug). A full log makes the DB read only, that's all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Perry Whittle

    SSC Guru

    Points: 233794

    i think the user was primarily confusing NOTRUNCATE and TRUNCATEONLY on DBCC SHRINKFILE with the BACKUP LOG scenario, i could of course be wrong but i'm sure this is the case 😉

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • david.washington

    Grasshopper

    Points: 16

    Great article. However, I think I would have pointed out one more point under your bulleted list

    AVOID TRUNCATING TRANSACTION LOG FILE ON PRODUCTION ENVIRONMENT BY ALL MEANS BECAUSE IT BREAKS LSN, unless:

    o You have a very high VLF count which is an indicator that your Transaction Log is fragmented. This fragmentation will cause performance issues.

    You should also mention something about growing your transaction log in chunks once you have it truncated so that it doesn't fragment again.

    Reference Kimberly Tripp's article at http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

  • Gail Shaw

    SSC Guru

    Points: 1004446

    No need to truncate the log and break the log chain to fix fragmentation. Pick a time the DB is not heavily used (not used at all is better) and the active log is at the beginning of the file. Take a log backup, shrink the log to 0, grow it back to the desired size in however many chunks you calculate for the VLFs you want.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • david.washington

    Grasshopper

    Points: 16

    That's true. You don't need to truncate the log to defrag VLFs.

  • Paul Randal

    One Orange Chip

    Points: 29438

    My goodness - what a load of utter nonsense in this article.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • danielfountain

    SSCarpal Tunnel

    Points: 4229

    Apart from the other stuff that has been said i would also suggest that from what i have seen the number one cause of the log file becoming big is that someone has left SQL on default settings and they dont know what there doing.

    So they should either

    A) Put it in simple - if that is what is required by the business\system. 99 times out of 100 i have seen systems that they want backed up nightly, and that is sufficient loss - but the database is in full mode.

    B) If on the rare occasion they do want more then nightly (sometimes you get "oh can you do that") then they need to be backing up the logs and maybe doing diffs. This is rare as people who normally want this backup regime know enough to actually know they need to implement it.

    Most times its a pop it in simple and the LSN`s are irrelevent to the backup regime they have.

    Dan

  • Perry Whittle

    SSC Guru

    Points: 233794

    david.washington (10/16/2012)


    Great article.

    just out of interest which article were you reading?

    Paul Randal (10/16/2012)


    My goodness - what a load of utter nonsense in this article.

    I actually didn't want to be that brutal, it does look like their first article. What was disappointing to me was that the authour linked in 3 separate authors (one of them yourself) and still managed to wander off base.

    What i would want the author to take away from this is to always fully research your article and if you're unsure then have someone peer review it

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Perry Whittle (10/16/2012)


    What i would want the author to take away from this is to always fully research your article and if you're unsure then have someone peer review it

    I'll go as far as to say always have the article peer-reviewed, especially if you're sure it's right (cause that's when you make the biggest mistakes). Every article I've had published here in the last 3 or so years has been reviewed by a couple people, and every time they found mistakes. Usually minor mistakes or missing explanations, but still stuff that needed fixing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • JeremyE

    SSCoach

    Points: 15195

    I might be missing something so I'm hoping someone can clear it up.

    I have used TRUNCATEONLY on log files in SQL 2008R2 before to remove VLF fragmentation. I double checked it against SQL 2012 and it seems to work still.

    DBCC SHRINKFILE(database_log, TRUNCATEONLY);

    I'm wondering if this is a BOL mistake, deprecated feature, or if it was never supposed to work.

  • Perry Whittle

    SSC Guru

    Points: 233794

    Books online states that if no target size is specified, a shrink-file operation removes only the inactive virtual log files beyond the last active virtual log file in the file. Depending on where the last active VLF is it could shrink a little or a lot, this is what you are seeing.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Paul Randal

    One Orange Chip

    Points: 29438

    No matter what size or options you specify, shrinking a log file can only ever remove inactive VLFs, and only from the end of the file.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Gail Shaw

    SSC Guru

    Points: 1004446

    JeremyE (10/16/2012)


    I might be missing something so I'm hoping someone can clear it up.

    I have used TRUNCATEONLY on log files in SQL 2008R2 before to remove VLF fragmentation. I double checked it against SQL 2012 and it seems to work still.

    DBCC SHRINKFILE(database_log, TRUNCATEONLY);

    I'm wondering if this is a BOL mistake, deprecated feature, or if it was never supposed to work.

    Sure, it works. What books online says is that the truncateonly option only applies to data files, so for a log file that's equivalent to

    DBCC SHRINKFILE(database_log);

    as the truncateonly option is completely ignored

    That means shrink the file as small as possible (given the current active VLFs) to the minimum of the size it was when it was created.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 26 total)

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