May 6, 2010 at 1:42 pm
I just dropped (in SS 2005) a large table (100+ G) and didn't get any space back within the database. How do I reclaim that space?
I know that if you drop a column you have to rebuild the indexes, but in this case there are no indexes to rebuild!
Thx for any help as I need that space back...
May 6, 2010 at 1:55 pm
Are you referring to the free space in a database, logical? Example executing sp_spaceused?
You may need to do a
USE <DB Name>
GO
DBCC UPDATEUSAGE
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 6, 2010 at 2:18 pm
MeltonDBA (5/6/2010)
Are you referring to the free space in a database, logical? Example executing sp_spaceused?You may need to do a
USE <DB Name>GO
DBCC UPDATEUSAGE
We had a gig or two of free space in the data file before I dropped this table. I then dropped the table and then looked to see how space was reclaimed. I specificially used the shrink in the MS GUI as it gives free space by file so you know how much you need to shrink by.
May 6, 2010 at 2:29 pm
Run this script to see how much free space you have in each database file, and how much space each table is using:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
Run this script to see how much space each database is using:
Get Server Database File Information
May 6, 2010 at 2:40 pm
We had a gig or two of free space in the data file before I dropped this table. I then dropped the table and then looked to see how space was reclaimed. I specificially used the shrink in the MS GUI as it gives free space by file so you know how much you need to shrink by.
You should be careful about shrinking your databases. Shrinking can cause a lot of fragmentation problems with your indexes. So if you do need to perform a shrink like that then you should probably reorganize all of your indexes afterwards if possible.
See the following for some more details if you like 🙂
http://www.sqlskills.com/blogs/paul/post/Why-you-should-not-shrink-your-data-files.aspx
May 6, 2010 at 2:51 pm
Whisper9999 (5/6/2010)We had a gig or two of free space in the data file before I dropped this table. I then dropped the table and then looked to see how space was reclaimed. I specificially used the shrink in the MS GUI as it gives free space by file so you know how much you need to shrink by.
I personally prefer to query system tables rather than using a GUI tool for this kind of tasks.
First post states table was 100+ Gig in size, next post states there where about 1 or 2 Gig free - Dropping such a comparatively large table will immediatelly show reclaimed space at database level.
Still not sure if poster is looking to reclaim space at database or at operating system level.
Not sure either about the "rebuild indexes" mention - if table got dropped there is nothing to rebuild, indexes are gone and for a very good reason.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 6, 2010 at 3:03 pm
Robert Biddle (5/6/2010)
We had a gig or two of free space in the data file before I dropped this table. I then dropped the table and then looked to see how space was reclaimed. I specificially used the shrink in the MS GUI as it gives free space by file so you know how much you need to shrink by.
You should be careful about shrinking your databases. Shrinking can cause a lot of fragmentation problems with your indexes. So if you do need to perform a shrink like that then you should probably reorganize all of your indexes afterwards if possible.
See the following for some more details if you like 🙂
http://www.sqlskills.com/blogs/paul/post/Why-you-should-not-shrink-your-data-files.aspx
You're preachin' to the choir. I just use the GUI to see how much free space is in the file and don't actually shrink the file.
May 6, 2010 at 3:04 pm
PaulB-TheOneAndOnly (5/6/2010)
Whisper9999 (5/6/2010)We had a gig or two of free space in the data file before I dropped this table. I then dropped the table and then looked to see how space was reclaimed. I specificially used the shrink in the MS GUI as it gives free space by file so you know how much you need to shrink by.
I personally prefer to query system tables rather than using a GUI tool for this kind of tasks.
First post states table was 100+ Gig in size, next post states there where about 1 or 2 Gig free - Dropping such a comparatively large table will immediatelly show reclaimed space at database level.
Still not sure if poster is looking to reclaim space at database or at operating system level.
Not sure either about the "rebuild indexes" mention - if table got dropped there is nothing to rebuild, indexes are gone and for a very good reason.
I want to reclaim space at a database level.
May 6, 2010 at 3:06 pm
Well, that's interesting. I just checked and it took awhile for it to show up. There is now 70+ Gig in the primary data file and 40+G in the secondary data file. So I've got my space back!
I don't know whether that was a stats thing or if there is some kind of cleaner thread I've never read about, but it's finally freed up!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply