Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
franco
Constraint Violating Yak Guru
255 Posts |
 Posted - 2003-01-31 : 04:28:27
|
| 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 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-01-31 : 15:23:54
|
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  HTHJasper Smith |
 |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-02-03 : 04:18:45
|
| Jasper, thank you very much for the clear explanation.Kind regards.Franco |
|
|
|
|
|
|
DTS - Recovery Model