ALTER DATABASE (optimization job)
From: Julie (anonymous_at_discussions.microsoft.com)
Date: 05/21/04
- Next message: John Bell: "Re: T-SQL Write to File FROM XML Explicit - delete job info"
- Previous message: David Portas: "Re: select fields width NULL"
- In reply to: Dimitris: "ALTER DATABASE (optimization job)"
- Next in thread: Tibor Karaszi: "Re: ALTER DATABASE (optimization job)"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 21 May 2004 03:47:21 -0700
Dear Dimitris,
This is one of the performance vr's cost issues that as a
DBA, makes your life fun. Firstly your plan will work (but
put in a shrinkdb in there after the backup on simple, but
the question is here is it worth it performance wise.
If you consider that the log file may grow over 17gb,
shrinking down to a couple hundred will really impact
perforance next time the re-indexing is run. The reason is
that as the log file grows it will attempt to get disk
space which will really slow your job down.
J
>-----Original Message-----
>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
>
>
>.
>
- Next message: John Bell: "Re: T-SQL Write to File FROM XML Explicit - delete job info"
- Previous message: David Portas: "Re: select fields width NULL"
- In reply to: Dimitris: "ALTER DATABASE (optimization job)"
- Next in thread: Tibor Karaszi: "Re: ALTER DATABASE (optimization job)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
