January 24, 2015 at 8:24 pm
As the title says I re-indexed all of my databases using the wrong fill factor. Instead of using 90% as the fill factor I misunderstood and set this at 10%. So I believe my databases are now packed with a ton of unused space. The DB sizes should be about 5-6 GB but have since grown to 20-40GB. I am very new to SQL administration and don't know of a safe way to remove this unused space so that my databases return to their normal sizes. The databases do not grow very much at all so the free space is not really that necessary. Any help would be greatly appreciated. Thank you in advance.
January 24, 2015 at 8:44 pm
If you backed up your databases before doing this, you could just restore over the bloated ones and be done with it. If not, you may have to use ShrinkDB... <shudder>
Be very careful with this, but you can shrink the database file. Be aware that it basically trashes your indexing, so you would have to rebuild your indexes afterward. Here's a good article on SHRINKDB. http://blog.sqlauthority.com/2010/08/12/sql-server-shrinkdatabase-for-every-database-in-the-sql-server/
I would probably find and download Ola Hallengren's reindexing code. Tons of people use it and it works a champ.
Personally, I would wait until someone really smart answers... but in order to reclaim your drive space, you're going to have to use ShrinkDB. Just don't go thinking that you should use it all the time. Read this if you think that's a good idea:
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
January 24, 2015 at 9:14 pm
Once you shrink the database, as pietlinden says, you'll need to rebuild all indexes. Despite how good many of the index routines written by various people are, a lot of them don't take into consideration what will occur to the log file if you're in the full recovery model.
The first thing that I'd do is to take a backup, as huge as it may be.
The next thing I'd do is set the database to the BULK LOGGED Recovery Model so that you can take advantage of minimally logged index rebuilds.
The other problem is that any index over 128 Extents (that's only 8MB) will be held in place until the new index has been rebuilt and committed. That means that your shrunk database will grow by that size and some more for some of the reindexing over head. What that means is that I'd rebuild the largest index there is first. That will likely be the clustered index on the largest table. You might also want to do the trick of disabling non-clustered non-unique indexes and then rebuilding them. That eliminates the problem of the old index being held while the new index is being rebuilt. Don't forget to do use the SORT IN TEMPDB = ON option to help in this area even more.
Also, consider the gravity of all of this. Unless you have the Enterprise Edition, you can't rebuild the indexes in an ONLINE fashion. That would actually be a good thing because when you do rebuild with ONLINE = ON, a mapping table is created possibly making the database even larger and the rebuilds will actually be a lot slower than if ONLINE = OFF even if you do have the Enterprise Edition. Oddly enough, rebuilding indexes with ONLINE = OFF also seems to do a much better job of defragmenting than when it' on. Also consider that you can't rebuild clustered indexes that contain blobs in the table or non-clustered indexes that include blobs in the INCLUDE anyway in 2012.
The reason why you don't want to do the "disable trick" on unique indexes is because those are frequently PKs and AKs and that would also disable any foreign keys pointing to them leaving you with a real mess to cleanup.
And don't forget to use the correct FILL FACTOR.
When you're done, change the database back to the FULL Recovery Model and do another FULL backup just for posterity sake. Of course, make sure that you have enough disk space to do those extra backups. Get the tape backup guys out of bed if you need to. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2015 at 9:18 pm
Crud. Almost forgot... I know it sounds a little weird but, even though the shrink will make a fragmented train wreck out of the database, consider doing the reindexes before the shrink and then again after the shrink. I have no quantitative proof but it seems that shrink works better when you free up pages by getting rid of the extra space (and you have a LOT because of the FILL FACTOR problem) on pages ironically caused by fragmentation of bad FILL FACTOR application.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2015 at 11:34 pm
You will have to re-index before you can shrink the databases, and then again afterwards to remove index fragmentation.
Although there is a lot of space available in the database, is is mostly in 90% empty database pages.
ShrinkDB will only remove completely empty pages, so you will need to do the following:
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.
January 25, 2015 at 9:06 am
Michael has good advice. I will amend it with a removal of the 90% fill factor recommendation. Many indexes in SQL Server can and should be 100% FF (or 95-100). Many others (GUIDs anyone?, partial-inserts-then-updated scenarios and some others) need to be 80-85% or even lower.
In the best case, EVERY index in your system will have a fill factor set specifically for that index for specific reason(s).
I do supposed if you can't do any research at all (like finding leading-column GUIDs) then 90% could be a decent middle-ground.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 25, 2015 at 11:38 am
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.
January 25, 2015 at 2:15 pm
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.
January 26, 2015 at 4:49 am
If you have space you could rebuild your indexes onto new files / file groups and simply drop the old files (bar the PRIMARY file) once the reindex has migrated all your tables to the new files. Primary could then be shrunk using dbcc shrinkfile (deflects flames :w00t:).
Should mean one iteration of index rebuilds rather than two which previous options suggest.
January 26, 2015 at 10:13 am
Conor Lillis (1/26/2015)
If you have space you could rebuild your indexes onto new files / file groups and simply drop the old files (bar the PRIMARY file) once the reindex has migrated all your tables to the new files. Primary could then be shrunk using dbcc shrinkfile (deflects flames :w00t:).Should mean one iteration of index rebuilds rather than two which previous options suggest.
Thanks guys again for the help. How would I go about dropping and recreating all indexes in the DB? The DBs are not accessed 24/7 so there is time for them to be brought offline. Thanks again.
January 26, 2015 at 6:01 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply