SQL Server Performance Forum – Threads Archive
DTS – Recovery Model
SQL Server 2000 SP3.Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small. Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases. 1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change? Any suggestion is really appreciate.
Thank you very much – kind regards. Franco
Franco
Are you saying that before SP3 you had no problem, but after adding SP3, you are now getting the new behavior? I am not aware of any changes that SP3 made like this (of course, who knows), but I would not have expected any changes like you describe. Have you verified that the mode has switched from full bulk-logged, or are you just assuming this? Perhaps you can explain a little more about what has happended, along with what you are doing, so we can better help. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
This is the explanation I have from another forum that makes light on the subject: The problem was that as well as switching on select into/bulk copy it also performed a dump tran databasename with no_log This invalidated your log backup chain and required a full database backup to be performed immediately to maintain recoverability. It was never intended to do this and there was no real indication on this (it was in the event log but it was not a documented side effect of the copy objects task) so this bug was fixed in SP3. As the transaction log is no longer truncated the log chain is kept valid. However it still uses bcp functionality and should be minimally logged BUT your transaction log backups will be much bigger. I have done testing to see that this was fixed but must say I haven’t checked the log sizes. I will do some testing and post back. Hopefully my above rambling explains why this change was made.
HTH
Jasper Smith Franco
]]>