| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2009-12-04 : 14:31:36
|
| We have a table, 900 k rows that is about 125 GB worth of storage size. It has a varbinary max colum in it. In order to reclaim space some people updated the varbinary columns to null on about half the records because the data in the column was unncessary but the record was still needed. The challenge we're having is reclaiming the space.Now before you fire off a response, here's what we've tried.Cleantableshrink dbdrop clustered index/recreateWe don't have enough disk space left to make a copy of the table and then move the data over either so I'm running out of ideas. This is on SQL 2005, SP2. Have at it!Mike"oh, that monkey is going to pay" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-12-04 : 14:40:34
|
| I was hoping you'd chime in ! Will try it out.Mike"oh, that monkey is going to pay" |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-12-04 : 14:59:22
|
| Didn't help taraMike"oh, that monkey is going to pay" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-12-04 : 15:20:35
|
| bah. You're supposed to have a magic wand for me tara! we have microsoft stumped too. So far they're recyclng the same ideas we've already tried. Question. Would creating a copy of the table and putting the new table and old table on the same partition using the same scheme function and then switching the partition to the new table do anything? I have a feeling I'd just be trading pointers to a new object id and not eliminate the issue but I don't know enough about the under the covers stuff with partitioning.Mike"oh, that monkey is going to pay" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-12-04 : 15:34:59
|
| Ah...funny when Prod is down all of a sudden people can give you access to servers. I just was given on (asked this morning!) Will try it out.Mike"oh, that monkey is going to pay" |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-12-04 : 16:34:31
|
| Well if anyone ever comes across this thread, the partition idea didn't pan out.Mike"oh, that monkey is going to pay" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-12-04 : 16:42:19
|
| How about setting the text in row option:sp_tableoption N'MyTable', 'text in row', 'ON'And then do an UPDATE to reset the varbinary column to null again:UPDATE MyTable SET binCol=Null WHERE binCol IS NULL |
|
|
|
|
Reclaim Space From Varbinary Change