February 14, 2011 at 9:23 am
I have a large table that is about 60% of my total database size and I am going to remove about 30% of the data resulting in about 80 GB of space that is going to get freed up.
I would really like to get the space back (mainly so my restores don't need the space before I shrink the database after it is restored for dev reasons). So I would like to shrink the datafile even though I know this isn't the best idea.
So if I were to shrink my database, what should I do besides defragment everything?
Thanks
Henry
February 14, 2011 at 10:14 am
I think your backups will be smaller just from deleting the data, so maybe the shrink won't make any difference to backup size.
February 14, 2011 at 10:18 am
It's not the backup that is the issue as much as it is the restore (it has to grab all that space)
February 14, 2011 at 1:06 pm
Even though Statistics are updated by the query optimizer, it is not a bad idea after a Index rebuild to run UPDATE STATISTICS
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
February 14, 2011 at 1:51 pm
Meet George Jetson (2/14/2011)
Even though Statistics are updated by the query optimizer, it is not a bad idea after a Index rebuild to run UPDATE STATISTICS
Actually, that is just a waste of time. When you rebuild an index, the statistics are updated with full scan for that index.
What isn't updated is the column statistics on columns that are not related to an index. These are either specifically created statistics (CREATE STATISTICS ...), or auto created statistics. In either case, you can issue the following to update only those statistics that need to be updated:
Execute sp_updatestats @resample = 'RESAMPLE';
This will update statistics with the last sampling rate for any statistics that are out of date.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply