March 13, 2006 at 4:12 pm
I am reading up on this issue and I have some questions that I am hoping someone who has experience in this kind of thing would be able to explain it to me.
On the microsoft website I found the following information - "Bulk-logging is more efficient than full logging and reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction"
Also, Index Defrag is listed under Index operation logging, as being fully logged regardless of the type of recovery model specified for the database.
Reading further I found this statement on the site - "The defragmentation of a very fragmented index can generate more log than a fully logged index creation. However, the defragmentation, is performed as a series of short transactions; therefore, it does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE.
So, if there was a job running defrag index once every week, would including a step to alter the database setting to SIMPLE recovery model from "FULL" and then changing it back to "FULL after the defrag help?
The part I don't quite understand is, if the defrag process is fully logged regardless of the recovery model, how does setting it to SIMPLE help?
Thanks
KR
March 14, 2006 at 9:12 am
The defrag is fully logged in a series of small transactions. In a SIMPLE database, this means that earlier transactions can be truncated and their space in the log re-used after each checkpoint. Similarly, in the other models, the earlier transactions can be truncated only after a log backup.
To prevent a log blow-up during the reindex, either solution (simple or log backups) works. Switching to simple and back will break any log backup chain, so your most recent recovery point is the time at which you switch to SIMPLE. If you take this approach, make sure you take an immediate full backup after the switch back to FULL recovery. Also, unless you can be sure no other transactions are hitting the database at the time, keep your resume up to date.
If your database is involved in log shipping you won't be able to set it to SIMPLE in the first place. Don't be afraid of taking frequent log backups; I backup all of my operational production logs every few minutes all day every day.
March 14, 2006 at 9:25 am
Thanks for the information. It is helpful to understand how this process works.
I did think Simple recovery was not such a good option and did not set it up this way although the third party vendor whose app it is that we need to run a defrag once a week recommeded we do so. What are your thoughts on Bulk-Logged recovery model? I don't think I can use it either if the database is involved in log shipping, is my understanding correct?
Thanks again.
KR
March 14, 2006 at 4:23 pm
Log shipping is supported in a Bulk-Logged database. The log backup includes both the logged transactions and the data pages from the bulk load operations. The 'minimally logged' activity means that references to the bulk loaded pages are logged, but the actual data is not. The log backup will pull the data pages from the database to get the data; therefore the log can remain small, but the log backups will be relatively large and are complete.
March 14, 2006 at 4:27 pm
Thank You! This information gives me a good starting point
March 29, 2006 at 1:10 pm
Does this mean that in a log shipping situation, minimally logged activity will still generate large transaction log backups to be shipped to the destination location?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply