Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question about shrinking data files on a read only database Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:35 PM
Points: 11, Visits: 167
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?
Post #1437464
Posted Monday, April 1, 2013 10:30 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 5,989, Visits: 12,925
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.


---------------------------------------------------------------------

Post #1437521
Posted Monday, April 1, 2013 10:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,135, Visits: 11,480
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



Post #1437525
Posted Monday, April 1, 2013 11:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:35 PM
Points: 11, Visits: 167
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.
Post #1437535
Posted Monday, April 1, 2013 11:17 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 5,989, Visits: 12,925
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.


---------------------------------------------------------------------

Post #1437543
Posted Monday, April 1, 2013 2:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:35 PM
Points: 11, Visits: 167
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.
Post #1437625
Posted Monday, April 1, 2013 4:51 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 5,989, Visits: 12,925
no problems, at least its a one off operation and the database can be used whilst you are doing the reorg.

---------------------------------------------------------------------

Post #1437656
Posted Monday, April 1, 2013 5:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:31 PM
Points: 56, Visits: 327
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
Post #1437664
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse