Re: Transaction log and rebuilding indexes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Chirag,

I would also make sure that I have a valid FULL backup before I change the
recovery model...

1) Check if the last FULL backup ran successfully
2) Make sure that Log backup is not running currently(else step3 would
error). Stop and disable the job.
3) Change recovery to SIMPLE
4) REBUILD ALL INDEXES using sp_msforeachtable 'ALTER INDEX ALL ON
?REBUILD'
5) Change Recovery to Full
6) Perform full backup
7) Enable Log backup job

hth,
AbbA

"c_shah" <shah.chirag@xxxxxxxxxxx> wrote in message
news:658b6199-b0da-4593-a15c-9caac97fec8e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have an *old* SQL 2000 database (SQL2K Standard), size of the
database is around 8 GB

in order to keep transaction log (current file size is 0.5GB) from
exploding, largest table in the db is around 1 GB

I am setting up the following tasks as scheduled job on Sunday at 1 AM
(database will not be going to used most probably at that time)

1) Change recovery to SIMPLE
2) REBUILD ALL INDEXES using sp_msforeachtable 'ALTER INDEX ALL ON ?
REBUILD'
3) Change Recovery to Full
4) Perform full backup

I can do index defrag instead of rebuilding them but
Is this the right way Or just increase the T-log to about 1 GB of
size?

Just to make sure ...?


.

Relevant Pages

  • Re: copying the hidden XP recovery partition
    ... containing a copy of XP Pro and what is called the "Product Recovery ... Not only would you "lost the only copy of the OS" should your HDD "go ... backup system that you would employ on a routine basis; ... I was wondering how you can copy the hidden Recovery disk ...
    (alt.comp.hardware.pc-homebuilt)
  • Re: How can I back up a log-shipped database?
    ... This means that a later log backup from the production database will not just be ... able to add the log records to the log-shipped database, because the transaction log has been ... It's clear I don't understand the whole RECOVERY business. ...
    (microsoft.public.sqlserver.server)
  • Re: Task Scheduler service - access is denied
    ... > preparation for doing the recovery from backup, ... Further, my backup software (Stomp's ... >>>Microsoft MVP (Windows Server System: ...
    (microsoft.public.windowsxp.security_admin)
  • Re: truncate transaction log
    ... recovery mode you must do a full backup to allow point in time recovery ... Here is how a bulk logged operation such as BCP of 1 million rows works. ...
    (microsoft.public.sqlserver.server)