SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question about shrinking data files on a read only database


Question about shrinking data files on a read only database

Author
Message
ErikMN
ErikMN
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 448
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?
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25158 Visits: 13701
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.

---------------------------------------------------------------------
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14815 Visits: 11848
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
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355
ErikMN
ErikMN
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 448
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.
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25158 Visits: 13701
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.

---------------------------------------------------------------------
ErikMN
ErikMN
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 448
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.
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25158 Visits: 13701
no problems, at least its a one off operation and the database can be used whilst you are doing the reorg.

---------------------------------------------------------------------
John M Dennis
John M Dennis
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 432
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search