November 6, 2009 at 11:12 am
Quick question, what is the best thing to do with a database to save space after after deleting large tables from it? Especially since the dbcc index defrag option is no longer supported in the sql server 2008. What about the database shrink option?
November 6, 2009 at 11:27 am
What is the Size of the DB and space available? the reason for the question is, in case you need to add new Data into the database, then you should not shrink the DB, since the File would again autogrow which is has performance impact and should be avoided.
So before shrinking decide about this.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 6, 2009 at 11:32 am
Its a bout 50 gigs and I don't plan on adding more data. I guess I just want to know how to get the space back because deleting a table doesn't guarantee that you have freed up space. Thanks for your input by the way
November 6, 2009 at 11:34 am
Hi
In this case shrink seems to be the correct solution.
Greets
Flo
November 6, 2009 at 11:37 am
Thanks a lot. So what if I planned on adding more data to the database daily? What would be the best solution then?
November 6, 2009 at 11:42 am
New data? Thought there will be no new data.. :doze:
In this case take Bru Medishetty's advice. Do not shrink the database. Delete old data take backups of your database and log files to release file usage and add new data.
Greets
Flo
November 6, 2009 at 12:26 pm
Thanks, I just wanted to know what would happen in the other scenario for future purposes. Thanks again guys !
November 7, 2009 at 3:18 am
I know I'm late to this party, but I have to say:
Only shrink if you absolutely must reclaim disk space.
Shrinking moves pages from the end of the physical files one at a time. Not only is that a slow (and fully logged) operation, but it is overwhelmingly likely that the moved pages will end up out-of-order as far as the object they belong to is concerned. So, shrinking is just about the best way to fragment data and indexes available. If you really really really can't avoid it, be sure to rebuild your indexes after shrinking. Bear in mind that rebuilding indexes (even if SORT_IN_TEMPDB is ON) will require workspace in the file...
Paul
November 8, 2009 at 12:30 pm
johnsonchase7 (11/6/2009)
<snip> Especially since the dbcc index defrag option is no longer supported in the sql server 2008.</snip>
Note that it's just the dbcc index defrag syntax that has been removed. Index defragging is now performed using ALTER INDEX ... REORGANIZE.
Eddie Wuerch
MCM: SQL
November 9, 2009 at 3:09 am
I'm curious whether truncating the table might not be a better idea here? If there are foreign key contraints that reference the table this won't work, of course, but then again the truncate option will reduce the usage of the transaction log (again, assuming that the user doesn't ever want to rollback the query!).
November 9, 2009 at 3:17 am
ta.bu.shi.da.yu (11/9/2009)
I'm curious whether truncating the table might not be a better idea here? If there are foreign key contraints that reference the table this won't work, of course, but then again the truncate option will reduce the usage of the transaction log (again, assuming that the user doesn't ever want to rollback the query!).
I think you may have misread the original question...?
I'm pretty sure we started off talking about how to reclaim space once entire tables were dropped from a database...;-)
November 9, 2009 at 3:56 am
November 9, 2009 at 4:00 am
ta.bu.shi.da.yu (11/9/2009)
So I did. Sorry.
No worries - I did exactly the same on another thread earlier...must be catching 😀
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply