Re: ALTER DATABASE (optimization job)

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 16:23:27 +0200

> My transactions in a day are about 4gb
> So why should i have a log of 17gb??

Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If you
do run regular log backups, then you could do something like:

1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db

You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@TK2MSFTNGP10.phx.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb??
>
> And the question isn't why.
> But how i can done it
>
> You can understand ofcource that if you have a log of 4 GB per day there is
> no reason to have a log of 17GB when you r trying to optimize your database.
> There is no need.
>
> Thanks
>
>
>
> "Dimitris" <seeyou_gr@hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@TK2MSFTNGP12.phx.gbl...
> > Hi all
> >
> > I have a database (warehouse) with a data file 16GB with Recovery model
> FULL
> >
> > And each week I do a night run for optimization with the options
> "Reorganize
> > data and index pages" and "Change free space per page percentage to 10%"
> >
> > When this night run occurs, the transaction log on my database increases
> to
> > 17GB
> >
> > cause to recreation of indexes and so on.
> >
> > I was wondering about the following, so I could solve the problem of 17GB
> > transaction logs. The disks are not cheap in an external sub-system with
> > mirrors and stripes.
> >
> > 1) Before the optimization job start backup the database
> >
> > 2) After the backup change the database recovery model to simple (so no
> log
> > will be recorded
> >
> > 3) Backup again database (now on simple mode) so transaction log will be
> > shrink
> >
> > 4) Run the optimization job
> >
> > 5) Change the database recovery model back to FULL
> >
> > 6) Backup again database (now in FULL mode)
> >
> >
> >
> > That's the solution I have thought.and all that will be done by the night
> > run
> >
> >
> >
> > How dangerous is to change the recovery model of the database before you
> run
> > a job?? Is the risk high??
> >
> >
> >
> > Is there an other way to perform the task, without having my transaction
> log
> > increased so match?
> >
> >
> >
> > Thanks in advance
> >
> >
> >
> > Dimitris Dimolas
> >
> > Web Programmer
> >
> > Greece
> >
> >
>
>

Relevant Pages

  • Re: ALTER DATABASE (optimization job)
    ... whether you perform regular log backups or not. ... If not, just run the db in simple recovery mode, and the ... > no reason to have a log of 17GB when you r trying to optimize your database. ... >> When this night run occurs, the transaction log on my database increases ...
    (microsoft.public.sqlserver.server)
  • Re: ALTER DATABASE (optimization job)
    ... whether you perform regular log backups or not. ... If not, just run the db in simple recovery mode, and the ... > no reason to have a log of 17GB when you r trying to optimize your database. ... >> When this night run occurs, the transaction log on my database increases ...
    (microsoft.public.sqlserver.tools)
  • Re: Questions to elicit systems requirements
    ... Automated failover of systems that allow a user to retry a transaction? ... Recovery requirements: ... How big is the database projected to be once fully in production? ... What method will the DBA be expecting to use to back the database up? ...
    (comp.unix.admin)
  • Re: Cant shrink log file
    ... There are several other criteria that needs to be met for the transaction ... backup log if you are in full recovery. ... ALTER DATABASE DEV SET RECOVERY BULK_LOGGED ...
    (microsoft.public.sqlserver.server)
  • Re: ALTER DATABASE (optimization job)
    ... > whether you perform regular log backups or not. ... If not, just run the db in simple recovery mode, and the ... Or shrink it, the article is just to explain side effects of shrinking! ... >>> When this night run occurs, the transaction log on my database increases ...
    (microsoft.public.sqlserver.server)