Re: Fragmentation Issues

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 06/23/04 

Date: Wed, 23 Jun 2004 14:52:17 -0400

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: sqlmaint Question
    ... Are there any long running open transactions that are preventing the ... What does DBCC OPENTRAN say? ... > Actually i'm not looking for it to shrink the file but actually clear the ... Previous to the backup the log was ...
    (microsoft.public.sqlserver.server)
  • Re: Cant shrink log file
    ... Did you look at DBCC OPENTRAN to determine if an open transaction is ... I was trying both and still it doesn't shrink. ... Did you know that a log backup and a full backup are not the same thing? ...
    (microsoft.public.sqlserver.server)
  • Re: Truncating
    ... You have to backup the transaction log and shrink the file. ... Based on the name column for the transaction log file execute the dbcc ...
    (microsoft.public.sqlserver.server)
  • Re: Database/Logs dont shrink with Maintenance Task
    ... Only a shrink file can do that but it may require a backup to get ... Other wise it has to keep growing to make room for more transactions. ... the log file smaller--whether you shrink or not? ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL 2005 Shrink issue
    ... you can't take a transaction log backup when your database in SIMPLE Recovery Model. ... shrink on the .ldf DB's in full mode? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
Loading