SQL Server Performance

Which recovery mode should I use?

Discussion in 'T-SQL Performance Tuning for Developers' started by pcsql, May 5, 2004.

  1. pcsql New Member

    I'm doing a lot of insert and update on permanent tables and I don't need to worry about rollback since it is one time job. I'm not sure whether I should set the recovery mode to bulk_logged or simple so it will cause less overhead. From what I have read from BOL, the bulk_logged only affects certain statement: SELECT INTO, CREATE INDEX, and bulk loading data operations.
    So, I guess I should use simple since I use Insert and Update statement.
  2. derrickleggett New Member

    I would use simple and make sure I have a full backup both before and after the process because I'm paranoid.


    When life gives you a lemon, fire the DBA.
  3. pcsql New Member

    Hi derrickleggett,

    Thank you for the reply. If I understand correctly, simple still log everything except the logs may be truncated automatically. So, it seems to me that using which recovery mode won't affect the performance of the Insert and Update. It will only affect when recovery is needed.
  4. derrickleggett New Member

    Depending on what you are doing, there are several operations that will perform faster if performed in bulk-logged or simple recovery mode. The log is not as robust and a performance increase is seen on several operations in the databases. My thought is that since it's a one-time thing set it to simple which uses the logs the least, keeps the logs small, and gives the additional performance.

    When you are done set the recovery mode back to full, run a full backup, and begin your transaction log backups again.


    When life gives you a lemon, fire the DBA.
  5. gaurav_bindlish New Member

    I second to what Derrick has suggested. There is no way you can stop the operations to be logged to the transaction Log as they are needed for recovery and for bringing the database to a consistent state. BULL LOGGED recovery model will only help if there are operations in the job which don't need the previous and new image of dat e.g. Create Index.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  6. smalldba New Member

    Hi All,
    Just a quick question, will the CREATE INDEX operation be logged in SIMPLE mode? I have read that it should not but I may be seeing diffrent results. I am trying to create a non-unique clustered index on a 5Gb table and my t-log has run out of space...

    thanks in advance.

Share This Page