DB Optimizactions

I set up Optimization weekly:
Reorganize data and index pages, change free spaces per
page percentage to 10%,
Remove unused space from database files, shrink database
when it grows beyond 50MB, amount of free space to remain
after shrink: 10%.
The job runs normally, but I still see my TL grow to >
1GB. Then I manually ran the job, expecting to see TL
file shrunk, but it didn't happen.
Does manually running Optimization shrink TL file size?
or I have to run DBCC SHRINKDATABASE?
Thanks.Locus Adam wrote:
> I set up Optimization weekly:
> Reorganize data and index pages, change free spaces per
> page percentage to 10%,
> Remove unused space from database files, shrink database
> when it grows beyond 50MB, amount of free space to remain
> after shrink: 10%.
> The job runs normally, but I still see my TL grow to >
> 1GB. Then I manually ran the job, expecting to see TL
> file shrunk, but it didn't happen.
> Does manually running Optimization shrink TL file size?
> or I have to run DBCC SHRINKDATABASE?
> Thanks.
I've only ever managed to totally shrink a logfile by setting the db
into simple logging mode.|||Yes this will stop TL from growing, but the trade off is
that you lose full recovery in case you lost your DB.
>--Original Message--
>Locus Adam wrote:
>> I set up Optimization weekly:
>> Reorganize data and index pages, change free spaces per
>> page percentage to 10%,
>> Remove unused space from database files, shrink
database
>> when it grows beyond 50MB, amount of free space to
remain
>> after shrink: 10%.
>> The job runs normally, but I still see my TL grow to >
>> 1GB. Then I manually ran the job, expecting to see TL
>> file shrunk, but it didn't happen.
>> Does manually running Optimization shrink TL file
size?
>> or I have to run DBCC SHRINKDATABASE?
>> Thanks.
>I've only ever managed to totally shrink a logfile by
setting the db
>into simple logging mode.
>.
>|||It makes absolutely no sense to shrink the file immediately after doing a
reorg. Shriking the file will most likely destroy or undo what the reorg
just finished doing. Uncheck the box that states to shrink the file. You
need lots of free space in the database and log for them to function
normally. If you are not using SIMPLE mode you must do regular log backups
to keep the log file from growing all the time and eventually it will get to
a point where it does not need to grow. That's where you want to keep it.
--
Andrew J. Kelly
SQL Server MVP
"Locus Adam" <anonymous@.discussions.microsoft.com> wrote in message
news:844c01c3f71a$ece3ac60$7d02280a@.phx.gbl...
> I set up Optimization weekly:
> Reorganize data and index pages, change free spaces per
> page percentage to 10%,
> Remove unused space from database files, shrink database
> when it grows beyond 50MB, amount of free space to remain
> after shrink: 10%.
> The job runs normally, but I still see my TL grow to >
> 1GB. Then I manually ran the job, expecting to see TL
> file shrunk, but it didn't happen.
> Does manually running Optimization shrink TL file size?
> or I have to run DBCC SHRINKDATABASE?
> Thanks.|||Andrew is correct. However, if you have extended the log file dramatically
due to some never to be repeated operation, you can shrink just the log file
and reclaim some disk space in that case. (Otherwise, you are running a Red
Queen's Race and never getting anywhere.)
Russell Fields
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ok9LW8x9DHA.1392@.tk2msftngp13.phx.gbl...
> It makes absolutely no sense to shrink the file immediately after doing a
> reorg. Shriking the file will most likely destroy or undo what the reorg
> just finished doing. Uncheck the box that states to shrink the file.
You
> need lots of free space in the database and log for them to function
> normally. If you are not using SIMPLE mode you must do regular log
backups
> to keep the log file from growing all the time and eventually it will get
to
> a point where it does not need to grow. That's where you want to keep it.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Locus Adam" <anonymous@.discussions.microsoft.com> wrote in message
> news:844c01c3f71a$ece3ac60$7d02280a@.phx.gbl...
> > I set up Optimization weekly:
> >
> > Reorganize data and index pages, change free spaces per
> > page percentage to 10%,
> >
> > Remove unused space from database files, shrink database
> > when it grows beyond 50MB, amount of free space to remain
> > after shrink: 10%.
> >
> > The job runs normally, but I still see my TL grow to >
> > 1GB. Then I manually ran the job, expecting to see TL
> > file shrunk, but it didn't happen.
> >
> > Does manually running Optimization shrink TL file size?
> > or I have to run DBCC SHRINKDATABASE?
> >
> > Thanks.
>