SQL Server Performance Forum – Threads Archive
Which recovery mode should I use?
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.
I would use simple and make sure I have a full backup both before and after the process because I’m paranoid. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
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.
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. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
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. Gaurav
Moderator
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.
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.
]]>