Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slimt_slimt
Aged Yak Warrior
746 Posts |
 Posted - 2010-04-18 : 09:22:30
|
| hi,i have a large table (in datawarehouse) with 80Mio rows.Against this table i have set 10 indexes, which obviously take a lot of space - 1Gb for each index (some up to 3Gb), where the whole table takes up to 180Gb disk space.If I drop index the size of mdf file doesn't drop. What shall i do in order to reduce the size of mdf file when i drop the index?and what is the best practice to keep this table not expanding so dramatically.i'm using sql 2005.thanx |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-04-18 : 09:50:12
|
To reduce data file size use this:DBCC SHRINKFILE (N'Your DB Name' , 0, TRUNCATEONLY) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-18 : 10:24:17
|
| I'd leave it alone and let the database re-fill the space that you have released.if you shrink it then it will have to regrow, and each regrowth will add fragmentation to the database.If you are desperate for the DISK space that would be made available then you'll have to shrink it, if you can live with the 180GB currently used by the database, but just want to extend the time before the files gets any bigger than that, then leave it as it is (and monitor the free space in the database so you can predict when it is going to fill up again) |
|
|
|
|
|
|
indexing large table