Reclaim space from Dropped Columns
-
Hi all,
I have a 50Gb database. One table (an Audit History table) in the database contains about 250 fields and about 15 million rows. Using sp_spaceused I can see that this table alone is using about 37Gb of the 50Gb total. I decided I could live without several of the columns in the table in order to make the database smaller. I dropped about 150 of the fields expecting a major reduction in the overall database size.
It remains unchanged!!! sp_spaceused tells me that the History table is still 37Gb. I have tried shrinking the database and update usage. To no avail.
Any help or advice on this is greatly appreciated.
Andy
-
Check out DBCC CLEANTABLE
This reclaims space from dropped text columns.
It can be set to run in batches as well so you can break it into chunks.
See what happens...
-
Thanks for the reply Shawn... however unfortunately all of the columns that have been dropped were numeric columns. DBCC CLEANTABLE only reclaims space from variable length (varchar and text) fields.
I am baffled by this, it seems bizarre that a table which should have been halved in size, hasn't changed at all.
Further suggestions appreciated... Thanks again Shwan
-
Ok, no probs.
A row must fit on a single page (8K) and has a maximum row length of 8060 bytes.
If, with 250 columns you had a row size of 3000 bytes and now you have 2900, then it's still 3 rows per page...
-
Is there a way to retrieve the row size of a table or is it a case of adding up all the data type sizes?
-
There are sizing tools on the net but it can be worked out.
Without delving into MSDN, I don't know the overheads per column, row, datatype etc off-hand.
With 240ish columns, it'd be a long job. Sorry!
-
No to worry Shawn... I appreciate the effort.
I have added up the datatype sizes for the shortened history table... it comes to 725 bytes. There are 13,918,290 rows... giving a total of 10,090,760,250 Bytes... approximately 10Gb. Table size is still 37Gb.
Previous row size would have been about 2200 bytes... giving a total space of 30,620,238,000, about 30Gb. Still not quite there, but a lot closer to the 37Gb that sp_spaceused is returning.
This 10Gb would really save my life. Anyone else got any ideas?
Thanks again
-
In which case, it should have shrunk...
Does the table have a clustered index? rebuilt it recently if so?
If not, create one (permanently or drop it afterwards)
Try DBCC SHOWCONTIG on the table to see how fragmented it is
-
I Find with really large tables rclaiming space is a bit of an issue when dropping columns.
I was puzzled before as to why i could not reclaim space from a table from QA but when I used Maintence Wizard, the tables indeed shrunk.
So I profiled exactly what Maint Wizard job was up to and found a Reindex with a hidden switch sorted_data_reorg.
DBCC dbreindex(@cTableName, N'', 90, sorted_data_reorg)
(90 is the fill factor). This could be what your looking for.
-
DBCC DBERINDEX is rebuilding an index anyway, hence my post and questions about index.
I prefer my own backup, index and statistic maintenance to that of the wizard, which does only runs sqlmaint anyway. Until recently, this had huge problems on dbs with computed columns so I stopped using it years ago. And as I gained experience I wanted to see what code I was running.
Looking at "sorted_data_reorg" in BOL shows it's not supported anymore, in theory, so I'd try a standard DBREINDEX first for going forward before using undocumented commands. Unless you really have to.
-
Yes your right Shawn, sorted_data_reorg was stopped being supported in SQL 7.0, used to be a feautre in SQL 6.5, but its still being used by sqlmaint. Standard DBCC reindex does not always work, this does.
I only suggested this as the poster seems desparate and nothing so far has worked for him. Best to use this feature to get out of a hole and take the pressure off, then can look at other "supported" alternatives.
which will probably come down to Dropping the indexes and re-creating them (which is a pain on a 50GB table). This is not much quicker, but fact is it is quicker than dropping and re-creating indexes. If I remember rightly it will table lock the table though so be aware of this. 
-
Thanks guys for the help.
I have resolved this by adding a primary key to the table. There were no indexes on the table at all
I have only recently inherited this db! In EM when you restructure a table it actually creates a new table with the changed structure and inserts from the existing table, drops the original and renames the new!The table is now 7Gb
So if it grows again, I will try a REINDEX (now that it has one) and it might help me shrink it!?
Thanks again for all the input.
-
You're welcome.
try and rebuild indexs on all dbs at least once a week, daily if you have teh maintenance window.
As much as I dislike it, the DB maint plan wizard is good start
-
Good grief, no indexes and no Primary key on a 50GB table?
How many weeks did it take to run a simple SELECT satement?
-
After DBCC DBREINDEX the space can be scattered, it wont reduce the size of the database file. Then have to shrink the database file by moving the free space using the shrink file TRUNCATEONLY command,
DBCC SHRINKFILE
-Maani
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply