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.
FrancoYikes!
If I read this correctly:
1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting
As far as I know, nothing regarding the database recovery model was changed in SP3. There were, however, many security changes to SP3. It may be possible that the switch to the Simple model is failing due to a permissions issue (the context under which DTS is running may not be permitted to alter database settings).
You do realize, of course, that your backups and transaction logs are useless after the DTS package runs? I mean, you can recover your data up to the point that the DTS package starts, but everything after that is toast?
I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences.
Hugh Scott
Originally posted by franco
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|||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|||RE:
Yikes!
If I read this correctly:
1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting
Q1 I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences. Hugh Scott
A1 As with many things, much depends on available resources, the nature of the application / purpose of the DB, the size / importance / purpose of the loads involved, etc., etc., etc..
For example, in some mission critical (and resource / budget constrained) production environments sometimes load schemes amount to something like: Full (final TL dump) --> Simple (dbo / single user) --> (perform data loads) --> Diff dump --> Full (multi user). Obviously, if large numbers of OLTP inserts / updates, etc. must be allowed in a fully logged fashion (7/24) such a scheme is not appropriate.
