Post reply

Bizarre transaction log growth during reindex

  • June 4, 2009 at 9:32 am

    #1004728

    Well kudos to george sibbald

    I ran the index rebuild while the db was in bulk logged mode without incident, no TL growth at all.

    Still don't understand why this would happen, but have a work around while I continue to investigate...

    thanks

  • June 4, 2009 at 9:50 am

    #1004741

    A Joy (6/4/2009)

    Well kudos to george sibbald

    ME 🙁 ?? 😀

    I ran the index rebuild while the db was in bulk logged mode without incident, no TL growth at all.

    Cos bulk logged minimally logs the bulk transactions, for more info check BOL

    Still don't understand why this would happen, but have a work around while I continue to investigate...

    thanks

    http://support.microsoft.com/kb/873235

    Go to the section : Prevent the transaction log files from growing unexpectedly

  • June 4, 2009 at 10:02 am

    #1004753

    Krishna Potlakayala (6/4/2009)

    A Joy (6/4/2009)

    Well kudos to george sibbald

    ME 🙁 ?? 😀

    I ran the index rebuild while the db was in bulk logged mode without incident, no TL growth at all.

    Cos bulk logged minimally logs the transactions, for more info check BOL

    Given, just confirming the behaviour seeing as previous behaviour was aberrant 🙂

    Still don't understand why this would happen, but have a work around while I continue to investigate...

    thanks

    http://support.microsoft.com/kb/873235

    Go to the section : Prevent the transaction log files from growing unexpectedly

    Not relevant as document suggests using the (soon to be) depreciated DBCC commands which dont allow online or sort in tempdb whereas I am using the alter table options..and I cant really cut a reindex job down further than a single index....without table partitioning anyway.

    I still stand by the comment that it shouldnt grow to 15gb....whatever the recovery model!

  • June 4, 2009 at 10:15 am

    #1004762

    A Joy (6/4/2009)

    Krishna Potlakayala (6/4/2009)

    A Joy (6/4/2009)

    Well kudos to george sibbald

    ME 🙁 ?? 😀

    I ran the index rebuild while the db was in bulk logged mode without incident, no TL growth at all.

    Cos bulk logged minimally logs the transactions, for more info check BOL

    Given, just confirming the behaviour seeing as previous behaviour was aberrant 🙂

    Still don't understand why this would happen, but have a work around while I continue to investigate...

    thanks

    http://support.microsoft.com/kb/873235

    Go to the section : Prevent the transaction log files from growing unexpectedly

    Not relevant as document suggests using the (soon to be) depreciated DBCC commands which dont allow online or sort in tempdb whereas I am using the alter table options..and I cant really cut a reindex job down further than a single index....without table partitioning anyway.

    I still stand by the comment that it shouldnt grow to 15gb....whatever the recovery model!

    That's Cool... but my focus was not on DBCC it was on how could you use bulk logged.

  • June 5, 2009 at 9:51 am

    #1005397

    I wonder if the ONLINE part of this is causing lots of extra tlog activity. See here, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/eef0c9d1-790d-46e4-a758-d0bf6742e6ae.htm and here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d82942e0-4a86-4b34-a65f-9f143ebe85ce.htm in BOL and also follow the various links on those pages for additional information.

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

Viewing 5 posts - 16 through 19 (of 19 total)

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