October 15, 2008 at 9:04 am
Hi All
I am having a table with 5 columns, with 2 big int columns, 1 int and 1 datestamp and 1 Varchar(max) datatype, size of the table is 65 GB initially, i have dropped column with varchar(max), which holds more data, but still my table is of same size, i have updated stats, but still dint help, what shall i do for SQL to release the disk space
I have used SP-Spaceused, still its not showing me the free space, there are no indexes on this table ( its a archive table and not used for any purpose :D)
Thanks in advance
October 15, 2008 at 9:24 am
CrazyMan (10/15/2008)
Hi AllI am having a table with 5 columns, with 2 big int columns, 1 int and 1 datestamp and 1 Varchar(max) datatype, size of the table is 65 GB initially, i have dropped column with varchar(max), which holds more data, but still my table is of same size, i have updated stats, but still dint help, what shall i do for SQL to release the disk space
I have used SP-Spaceused, still its not showing me the free space, there are no indexes on this table ( its a archive table and not used for any purpose :D)
Thanks in advance
AFAIK tables won’t release space when you drop a column. It will release space when you rebuild the table or when you truncate the table. I don’t think that it will release space in any other situation (but I might be wrong here).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 15, 2008 at 9:33 am
Thanks Adi, i have the same doubt, i might be wrong, wondering if there is any ways on 2005 to do this
🙂
October 15, 2008 at 11:15 am
What if columns are retained, but values are set to NULL ??
Will SQL reuse that space for other data needs ? Or is it only available to that table if the columns get repopulated ?
October 15, 2008 at 11:18 am
Unused space cannot automatically released.
Did you try DBCC SHRINKDB command?
October 15, 2008 at 11:26 am
yes i tried, but it dint work, i also tried to shrink files, i dint work either, i read some where about DBCC Cleartable, i am reading about that, not sure about the command, need to check first, please let me know if there are any other methods as well
Cheers
🙂
October 15, 2008 at 11:44 am
DBCC CLEANTABLE reclaims space after a variable-length column or a text column is dropped by using the ALTER TABLE DROP COLUMN statement. The command does not reclaim space after a fixed-length column is dropped
October 15, 2008 at 11:46 am
How about image data ?
October 15, 2008 at 11:50 am
think that this problem has been faced only when you drop the table column, SQL reuses the space without releasing this, not sure about null's
🙂
October 15, 2008 at 12:00 pm
Wheather or not data is NULL does not matter to the space in data pages. Only does DATATYPE affect it.
October 15, 2008 at 12:22 pm
Please, please stop shrinking databases, it doesn't help. Almost never is it worth doing.
This is an interesting question. When is the space released? It's really when are the extents deallocated for that particular object. At that point I'd think they could be reused by other objects and the space for the table should be correct.
October 15, 2008 at 12:40 pm
In case you have clustered index on this archive table then try Rebuilding the index(make use of ONLINe=ON if required or do it offline).
HTH
MJ
October 16, 2008 at 4:33 am
...so you want the space back after dropping a column?
Just reorganize e.g. move the affected table.
In regards to all those posts talking abour shinking the database... pals/gals... what has possesed you? poster only wants to get space back on the free list after dropping a column, how shrinking the database -which is usually very bad mojo - would help? :doze:
_____________________________________
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.October 16, 2008 at 7:43 am
CrazyMan (10/15/2008)
Hi AllI am having a table with 5 columns, with 2 big int columns, 1 int and 1 datestamp and 1 Varchar(max) datatype...
Since you have already dropped the varchar(max) column there should be plenty of space within the data file. If you need to shrink the file (and I know there are times when you just have to do this at least in the short term) then create a second table with the same structure (minus the varchar(max) column) and INSERT INTO TABLE2 (columns) SELECT(columns) FROM TABLE1 Then drop the original table. Rename if desired.
You'll find that shrinkfile works fine after that. Given the size (65Gb) you might want to consider running the shrinkfile in a loop knocking off a small amount each time.
Following this you'll need a hefty rebuild of indexes.
October 16, 2008 at 8:56 am
Thanks Guys, I have no Indexes, its a Heap table, As i said i tried to Clean the table using DBCC CleanTable Command, this took me lots of log space, so i have to stop this process. i can copy it to a new table this will reduce the space, but its more than 60GB, there is a space restriction( considering the space will be less without the column as well). i am trying to get the cleantable to clean it in chunks, will let you know how it went.
Any other Ideas that i can do this???
Cheers
🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply