April 1, 2013 at 8:36 am
I've got a database that is read only that a couple times a month needs to be transferred from the development environment (local) to our production environment (remote). The database is large (150+ GB) with about 75GB of that being free space that is from when the indexes are created.
I'd like to be able to recover some of that space to minimize the footprint of the database, however after shrinking the data files the indexes on the large tables end up very fragmented as you would expect and whenever I reorganize or rebuild them (using SORT_IN_TEMPDB = ON), the database grows to 150GB. Is there any way to minimize the size of the db and have unfragmented indexes or am I just stuck with that unused space?
April 1, 2013 at 10:30 am
just some thoughts...
is most of the database made up of one table?
what fill factor do you use when you rebuild, as the database is read only you can use 100%
do you rebuild all indexes regardless or only those above a certain fragmentation.
whats the growth factor on the data file, keep that a smallish( 500ish) MB value rather than a percentage.
---------------------------------------------------------------------
April 1, 2013 at 10:51 am
1. Shrink the database.
2. Reorganize (defragment) all tables.
3. Repeat 1 and 2 until there is no more progress.
Do not use rebuild (re-index), since that may cause the data file to expand.
Make sure you are in simple recovery mode to minimize the growth of the log file.
You can use the script on this link for the shrink:
Shrink DB File by Increment to Target Free Space
April 1, 2013 at 11:05 am
There are a handful of tables in the database but one of them is about 80% of the data and another is about 15% so those two tables hold most of the data.
As for the fill factor, I had it at 100 when I last rebuilt it because the database is read-only.
I've been rebuilding all of the indexes in my testing mainly because the only indexes not fragmented are on tiny tables. Both large tables have 90%+ fragmentation after shrinking the DB.
The growth factor is set to 1GB I could lower it, but I assume that would only save me at most 500MB.
April 1, 2013 at 11:17 am
ErikMN (4/1/2013)
There are a handful of tables in the database but one of them is about 80% of the data and another is about 15% so those two tables hold most of the data.
thats the root of your problem
try reorganise only on these tables after shrink.
---------------------------------------------------------------------
April 1, 2013 at 2:57 pm
Ok, I'm doing a reorg on both large tables and given that we've always done "smart" index maintenance on our read-write servers it's very interesting to see how much longer a reorg takes than a rebuild on a severely fragmented index. It took about 1:45 to rebuild all the indexes in the database and so far I'm at almost 4 hours to reorg the main index on the larger of the two tables. That being said, the data files have not grown at all, so that's good.
Anyway, thanks for the help.
April 1, 2013 at 4:51 pm
no problems, at least its a one off operation and the database can be used whilst you are doing the reorg.
---------------------------------------------------------------------
April 1, 2013 at 5:23 pm
If you want to reclaim or rather not double your allocation for the rebuild then drop and then create your indexes. Or, Disable the nonclustered index and then rebuild.
http://msdn.microsoft.com/en-us/library/ms179542(v=sql.105).aspx
or google DROP CREATE vs. REBUILD for more explanation.
Cheers,
John M Dennis
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply