Home Forums SQL Server 2012 SQL 2012 - General Re-Indexed with the Wrong Fill Factor and Now All Databases Are Huge RE: Re-Indexed with the Wrong Fill Factor and Now All Databases Are Huge

  • severnj (1/25/2015)


    Hi,

    Thank you all for the help on this. I tried the below listed steps but maybe I am doing something wrong? I used the built in Management Studio Re-Index maintenance plan on a database using a fill factor of 95%. Then ran the script from the link you provided setting the MB in free space after shrink to 100MB and the increment to 50MB. The database shrunk by perhaps 2GB but it really should have been cut by out 7GB. Maybe the built in maintenance plan re-index isn't doing what I need it to do? If so is there another script I should be running to re-index before and after the shrink script? Thank you again for the assistance.

    *We are using simple recovery mode.

    Jeremy

    1. Reindex all tables using the 90% fill factor to free up empty pages that can be removed by the shrink.

    2. Shrink the database leaving a little bit more free space than the size of the largest table in each database.

    You may find the script on the link below useful to make the shrink process a little less painful.

    Shrink DB File by Increment to Target Free Space

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

    3. Reindex all tables again using the 90% fill factor.

    The shrink may be causing the tables to fragment badly and leaving a lot of half empty pages. I would suggest running through all three steps again, but setting the shrink increment to 500 MB and target free space to the size of the largest table in the database. Sometimes it is necessary to do multiple iterations to reclaim all of the space.

    Also, make sure that all tables have clustered indexes and you are selecting all indexes.