Re: Fragmentation Issues

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

From: Thomas Brooks (jr_at_jfj.com)
Date: 06/23/04 

Date: Wed, 23 Jun 2004 16:38:50 -0400

My backup schedule is a full each night in the early morning. A Tran log
backup (which as you know truncates the log) is run each day at 1:00p. So
if I do a full backup and then trucate the tran log I have all I need to
restore.

The steps I performed in the last paragraph were the only ones that got rid
of all the serious fragmentation. The DB optimization tasks in the
maintenance plan never did. I don't know why, but it didn't. Do you know
why the DB maintenance plans don't remove fragmentation?

What about the following?
> > dbcc checkdb
> > backup database <to have most current data>
> > dbcc reindex (<tables>,'',80)
> > dbcc shrinkfile(<db>,100)

Do you recommend never shrinking the DB -or- when would you recommend
shrinking a DB?

Thanks for you help.

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:%23nPr3MVWEHA.3044@TK2MSFTNGP10.phx.gbl...
> Thomas,
>
> A couple of comments here about your last post. You don't need to run
> checkalloc if you just ran CheckDB since that is included in checkDB. Why
> do you backup the log with Truncate_Only? You now loose the ability to
> recover to a point in time. If your going to do that then you may as well
> just change the recovery mode to simple and be done with it. Otherwise
> always do a regular log backup so you can use it for what is intended for.
> And lastly don't shrink your database. Your in a vicious cycle by
> reindexing and then shrinking. The database needs extra free space to do
> the reindex and then when you shrink it you destroy most of the work you
> just did by defragging it. Free space in a database is a good thing and
> necessary for proper operation.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Thomas Brooks" <jr@jfj.com> wrote in message
> news:%23YLHGAVWEHA.1340@TK2MSFTNGP10.phx.gbl...
> > Jaxon:
> >
> > One question....I had the following two DB maintenance plans. They run
in
> > the below order.
> > 1) Full Backup
> > 2) Plan to reindex and set fill factor to 90% <AND> shrink DB if above
50M
> > 3) Check consistency.
> > I have always noticed that fragementation was above for the most part
90%.
> > It never decreased even after running the above. I also noticed my data
> > files kept growing and the trans log file size never decreasing even
after
> a
> > full backup which truncates the logs.
> >
> > Well today I performed the following steps and now frag is under control
> and
> > db file sizes are under considerably better. Why didn't the above do
what
> I
> > thought and why did the below accomplish what I thought the above would
> do?
> > dbcc showcontig <to get current stats>
> > dbcc checkdb
> > dbcc checkalloc
> > backup database <to have most current data>
> > backup log with truncate_only
> > dbcc shrinkfile(<db>,100)
> > dbcc reindex (<tables>,'',80)
> >
> > Now, my logical and extent fragmentation is 0, the data and tran log
files
> > have been reduced (trans log to 20MB).
> >
> > Finally, can I get the same result by using shrinkdatabase rather than
> > shrinkfile? It looks like the DB maint plan uses the shrinkdatabase.
But
> I
> > never see it change a thing. Also, if you are wondering...I have always
> > checked the job logs and they all execute without a hitch.
> >
> > Thanks for your time.
> >
> >
> > "Thomas Brooks" <jr@jfj.com> wrote in message
> > news:e8CXPtUWEHA.2996@TK2MSFTNGP12.phx.gbl...
> > > Can you zip it? OE removed the attachment and said it was unsafe.
> > >
> > > Thanks.
> > >
> > > "Jaxon" <GregoryAJackson@hotmail.com> wrote in message
> > > news:e2j8npUWEHA.3988@tk2msftngp13.phx.gbl...
> > > > read this article I wrote a while back for SQLServerCentral.com. It
> > tells
> > > > you all you need to know
> > > >
> > > >
> > > >
> > > > GAJ
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Fragmentation Issues
    ... Tibor Karaszi, SQL Server MVP ... > backup is run each day at 1:00p. ... >>> dbcc checkdb ... > Do you recommend never shrinking the DB -or- when would you recommend ...
    (microsoft.public.sqlserver.server)
  • Re: Tran Log Growth high tried truncating and shrinking
    ... And only after trans ... > have been committed and a full backup taken. ... It is not the shrinking that is harmful it is the growth that will ... temporarily halt any new transactions until the growth is complete. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log too big
    ... For SQL Server backups, the file size is not what determines the size of the backup. ... and I also agree that shrinking has a place for "unregular" operations. ... > that's empty though - the physical file will still have the same size on ...
    (microsoft.public.sqlserver.server)
  • 2 Qs: DB Shrink and Agent step
    ... Q1: shrinking transaction log ... backup log file with successfully, ... SQL Server Agent only runs step 1 ...
    (microsoft.public.sqlserver.server)
  • Re: Corrupt backup - "An internal consistency error occurred", code 42000
    ... Yesterday, DBCC CHECKDB reported no errors on the 'base, though, and then I ... I was able to successfully mount the database by manually attaching the mdf ... showed no errors), and did a backup. ... I would expect that DTS operations would ...
    (microsoft.public.sqlserver.server)