Correct senquence for reindex and shrink.

Question

Post reply

Correct senquence for reindex and shrink.

Jose Ianuck

Right there with Babe

Points: 776
More actions
December 17, 2008 at 7:08 am

#195224

Hi, I created a procedure that is execute the commands in this sequence.( 1-DBCC REINDEX, 2-Update Statistics, 3-Re-Compile and 4-Shrink ). Talking with my friends, I think that this sequence is not correct, because when I put the fillfactor 80% in reindex and the last command Shrink, the free space of 20% will be lost, I think that pages reserved are return for Operational System.
Thanks.
Ian.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

1. Shrink

2. Re-Index

Shrinking causes fragmentation in the data/indexes. so you need to re-index after shrinking.

Steve Jones - SSC Editor SSC Guru Points: 736607 More actions · Answer 2

You need free space in the database for the reindex.

You should not shrink the database on a regular basis. This is an emergency procedure if something caused the database to grow larger than it needs to be. There should always be free space inside the data files to allow for growth and maintenance.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

For most cases the correct way would be NO SHRINK.

If you search around you will find loads of posts why shrinking your database is not recommended.

[font="Verdana"]Markus Bohse[/font]

steveb. SSC-Forever Points: 46733 More actions · Answer 4

Yes the best advice would be to not run this code at all, esp. on a production box

check out this excellent article on db shrinking and the problems it can cause.

http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

In addition, if you decide that you need to manually update statistics, do it before the reindex, not after. Reindex updates all index statistics with full scan. If you run a sampled stats update afterwards you will be decreasing the accuracy of your stats.

So, in summary.

Update stats (if necessary), reindex DB. Don't shrink.

What do you mean by recompile?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jose Ianuck Right there with Babe Points: 776 More actions · Answer 6

Recompile or SP_RECOMPILE, is the stored procedure that force a recompile of a stored procedure the next time it is run. ok!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Jose Ianuck (12/17/2008)
Recompile or SP_RECOMPILE, is the stored procedure that force a recompile of a stored procedure the next time it is run. ok!

Absolutely unnecessary. The reindex will force all stored procs based on the reindexed tables to recompile on next execution. So, for that matter, will a stats update.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jose Ianuck Right there with Babe Points: 776 More actions · Answer 8

Thank's let's me know, it is so good information.

Att,

Ian.