09-22-03, 23:04 #1Registered User
- Join Date
- May 2003
Unanswered: CHanging Recovery Options SQL 2000
I want to run a Re-Indexing job against a database that normally has Tran Log backups ( it uses FULL recovery mode ) on SQL 2000. But when this job runs, it causes HUGE tran logs and huge tran log backups ( which then use up 99% of our disk space).
The job needs to be run, so I thought of changing recovery mode to SIMPLE, then run the job , then changing the recovery mode back to FULL. ...........Problem solved, you would think......
But, if the database were to corrupt after the job ( i.e. hours later ) where do I stand with what was in the tran logs both before the revovery mode change and then after? i.e. when I change to SIMPLE does the contents of the tran log get written to the db
By the way, we normally run tran log backups until 8am-6pm then do the re-indexing job at 4am and re-start the tran log backups at 8am.
Thanks for any help,
09-23-03, 07:26 #2Registered User
- Join Date
- Oct 2001
- Naples, FL
Safest way would be to backup the DB, change the recov mode, run the reindex, change recov mode back and back the db up again.
Never read it anywhere but pretty sure if you change the recov mode it would checkpoint the tran log.
Ray Higdon MCSE, MCDBA, CCNA