Sunday, March 11, 2012

"Simple" recovery model?

SQL 2000 SP3a on W2K SP4 server. I've created a new database with only three
tables and set the Recovery Model to Simple. The reason being, these tables
are essentially "temp" tables who's data will be constantly deleted and
re-inserted. Since it is all calculated data, I have no need for recovery of
any kind. My problem is this: Even with the Recovery Model set to Simple,
the Transaction Log grows exponentially. This is a real problem for me since
these tables may be emptied and re-populated 1,500 times in one evening.
What recovery model should I use in this situation in order to keep the Log
file size under control? Or should I just run SHRINKDATABASE or some other
utility after each cleaning?Hi
Keep your transactions short and sweet. If it is all one transaction, and
you update 10 million rows, you will need a lot of space.
Shrink DB will not help unless the transaction is committed or rolled back.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O5ijLRPAGHA.3136@.TK2MSFTNGP15.phx.gbl...
> SQL 2000 SP3a on W2K SP4 server. I've created a new database with only
> three
> tables and set the Recovery Model to Simple. The reason being, these
> tables
> are essentially "temp" tables who's data will be constantly deleted and
> re-inserted. Since it is all calculated data, I have no need for recovery
> of
> any kind. My problem is this: Even with the Recovery Model set to Simple,
> the Transaction Log grows exponentially. This is a real problem for me
> since
> these tables may be emptied and re-populated 1,500 times in one evening.
> What recovery model should I use in this situation in order to keep the
> Log
> file size under control? Or should I just run SHRINKDATABASE or some other
> utility after each cleaning?
>

No comments:

Post a Comment