Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    220

    Angry Unanswered: CHanging Recovery Options SQL 2000

    Howdy,

    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
    or what?

    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,

    SG

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    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.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •