Re: Fragmentation Issues

• Previous message: Jeff Duncan: "Re: Installing Office 2000 on SQL 7"
• In reply to: Thomas Brooks: "Re: Fragmentation Issues"
• Messages sorted by: [ date ] [ thread ]

Date: Wed, 23 Jun 2004 22:53:55 +0200

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

Why shrink it?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Brooks" <jr@jfj.com> wrote in message news:ubLnYIWWEHA.2840@TK2MSFTNGP11.phx.gbl...
> 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
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

• Previous message: Jeff Duncan: "Re: Installing Office 2000 on SQL 7"
• In reply to: Thomas Brooks: "Re: Fragmentation Issues"
• Messages sorted by: [ date ] [ thread ]