<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Search:
Home
Articles
Editorials
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Advertise
Write for us
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Strategies
»
Deleting Large Tables
13 posts, Page 1 of 2
1
2
»»
Deleting Large Tables
Rate Topic
Display Mode
Topic Options
Author
Message
johnsonchase7
johnsonchase7
Posted Friday, November 06, 2009 11:12 AM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 15,
Visits: 21
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?
Post #815097
Bru Medishetty
Bru Medishetty
Posted Friday, November 06, 2009 11:27 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 559,
Visits: 464
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.
Regards,
Bru Medishetty
www.LearnSQLWithBru.com
Post #815107
johnsonchase7
johnsonchase7
Posted Friday, November 06, 2009 11:32 AM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 15,
Visits: 21
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
Post #815109
Florian Reischl
Florian Reischl
Posted Friday, November 06, 2009 11:34 AM
SSCommitted
Group: General Forum Members
Last Login: Today @ 2:28 AM
Points: 1,966,
Visits: 3,360
Hi
In this case shrink seems to be the correct solution.
Greets
Flo
The more I learn, the more I know what I do not know
How to Post Data/Code to get the best Help
"Numbers" or "Tally" Table
How to Post Performance Problems
Post #815110
johnsonchase7
johnsonchase7
Posted Friday, November 06, 2009 11:37 AM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 15,
Visits: 21
Thanks a lot. So what if I planned on adding more data to the database daily? What would be the best solution then?
Post #815115
Florian Reischl
Florian Reischl
Posted Friday, November 06, 2009 11:42 AM
SSCommitted
Group: General Forum Members
Last Login: Today @ 2:28 AM
Points: 1,966,
Visits: 3,360
New data? Thought there will be no new data..
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
The more I learn, the more I know what I do not know
How to Post Data/Code to get the best Help
"Numbers" or "Tally" Table
How to Post Performance Problems
Post #815116
johnsonchase7
johnsonchase7
Posted Friday, November 06, 2009 12:26 PM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 15,
Visits: 21
Thanks, I just wanted to know what would happen in the other scenario for future purposes. Thanks again guys !
Post #815137
Paul White
Paul White
Posted Saturday, November 07, 2009 3:18 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 3:09 PM
Points: 2,069,
Visits: 2,039
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
The quality of the answers is directly proportional to the quality of the question.
Post #815371
Eddie Wuerch
Eddie Wuerch
Posted Sunday, November 08, 2009 12:30 PM
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 322,
Visits: 568
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
MCT; MCTS (SQL-2005, SQLDev-2008, DBA-2008), MCITP (SQLDev-2005, DBA-2005, DBA-2008)
MCDBA, MCSD
sql@mirai-tech.com
Post #815595
ta.bu.shi.da.yu
ta.bu.shi.da.yu
Posted Monday, November 09, 2009 3:09 AM
SSC Veteran
Group: General Forum Members
Last Login: 2 days ago @ 3:13 AM
Points: 202,
Visits: 383
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!).
Random Technical Stuff
Post #815694
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2009 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use
