SQL Server Blogs

Voices from the UK SQL Server Community
Welcome to SQL Server Blogs Sign in | Help
in Search

My two cents

Tips, opinions, tests (and misspellings) from a Brazilian DBA in UK

September 2008 - Posts

  • Reducing Transaction Log fragmentation

    I am taking Kimberly Tripp and Paul Randal's course on SQL Server Performance & Availability this week in Hatfield and we had an interesting discussion on Transaction Log internal fragmentation. Kimberly summarized the issue, together with other Transaction Log best practices, here (check item 8). Based on that discussion, and as I was bitten by this issue before, I thought it might be interesting to write a procedure to help verify and fix it.

    The procedure is called sp_DBA_DefragLog and will report your current fragmentation and fix it, recreating your transaction log in reasonably sized increments (as creating huge transaction logs in one go can have its issues as well). The procedure has the following syntax:

    sp_DBA_DefragLog { @Action=['Report'|'Simulate'|'Fix'] } {, @TargetMb = value}

    Where @Action='Simulate' prints the commands the 'Fix' would execute. @TargetMB specifies the desired size for the Transaction Log at the end of the process. If none is specified, the current size is kept.

    It is important to note that, for simplicity, I decided to leave to the admin the task of clearing its transaction log if the database is on FULL or BULK-LOGGED. In this case you probably should run your periodic log backup before running the sp with Fix option.

    I  don't have instances of SQL2005 and SQL2000 to test right now, but I expect it to run on 2005 (some adjustments might be necessary). SQL 2000 will need some more work, which I plan to do when I'm back at work, as I don't have SQL2000 on my notebook anymore.

    BTW, If you can, I definitely recommend taking Kimberly and Paul course. There were a lot of very interesting discussions that if I were less lazy would have resulted in at least half a dozen blog posts.

Powered by Community Server (Commercial Edition), by Telligent Systems