Post reply

turn off transactional logging

  • March 22, 2007 at 7:21 am

    #75139

    How would I turn off transactional logging for an application upgrade?  Does that mean setting the database in bulk log mode?

    Thanks!

  • March 22, 2007 at 7:53 am

    #696366

    Hello Mc,

    In order to turn off transactional logging, you need to change the recovery model to "Simple" and not "Bulk Logged".

    Go through this link and you will find the details to understand further.

    http://msdn2.microsoft.com/en-us/library/ms189275.aspx

    Hope this helps you and do the required upgrade to your application.

    Thanks

     


    Lucky

  • March 22, 2007 at 8:07 am

    #696370

    I think that changing recovery model to "Simple" still does not prevent the growth of the transaction log (if this is what you are trying to avoid).

  • March 22, 2007 at 8:27 am

    #696381

    Thanks for your help.

  • March 23, 2007 at 6:30 am

    #696586

    You can set the recovery model to simple (truncate log on checkpoint), but the transaction log will still be used and can grow significantly.

    This option will truncate the transaction log (internally, not the physical file size) every time there is a checkpoint done in the database.  Checkpoints are generally automatic and are rarely done manually.  However, the smallest thing you can checkpoint is a single transaction.  So, if you have a single transaction with 2gb of logged operations, you will still end up with a 2gb log file when the checkpoint runs.

    There is basically nothing you can do about this.  If the transactions were not logged and held until a checkpoint, you would not  be able to recover from the SQL server being stopped mid-transaction.

  • March 23, 2007 at 1:07 pm

    #696696

    Michael - very well described!

    One possible thing to do [to add to what you said] would be to process things in chuncks.  If, for example ,you need to perform a huge insert, which would result  in 2gb of trans growth, you can insert 1/100th at a time, backing up transaction log after each such insert.  This, while resulting in 100 transaction log backups, should keep trans log at a size = (current + growth due to insert of 20 mb)

Viewing 6 posts - 1 through 5 (of 5 total)

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