Shrinking Database question

[pvmurphy]

I had a DB with about 21 GB’s of data. First I deleted a lot of temporary tables cluttering up the DB and then I performed the shrink database job from Enterprise Manager, allowing 10% free space after shrinking. After the job completed, my data file still is close to 21 GB’s, but my backup to disc has been reduced to 14GB. The backup file makes sense, but how come the data file didn’t reduced also. What do I need to do to reduce the size of the data file also?

Thanks,

Jim

 

[nigelrivett]

did you take the
"move pages to the begining before shrinking" option?


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[pvmurphy]

Was not really sure what each option did for me, so I kept pretty much to the defaults, except for changing the amount of maximum free space in file after shrinking to 10%, the default here is 0, but is it advisable to set it to 0?).
Moving pages to the beginning states that it could hinder performance, so I was hesitant to do that. Does that statement meant while your shrinking the database, or even after when it was done.

Thanks

 

[nigelrivett]

It means before the shrink.
If there is data at the end of the file then it can't shrink - this defragments before the shrink.

Setting free space to 0 means that the database will have to grow again but if you have left it at 10% growth then you are only saving one expansion.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[pvmurphy]

Thanks for the advise, I was able to recover over 7 GB's of space.

Jim