Allocations of LOB datatypes

All
We have a very large table (1M+ rows) in which we are
storing large xmls (datalength(column) of 10K) in a text
datatype field.
We are using up space at a much greater rate than
originally planned - and will stop saving the the xml
for certain events. There are about 25 events during the
lifetime of an order.
I am trying to understand how we can get back the space
allocated to this object if we decided to set the column = null. It does not seem to change the information returned
by sp_spaceused even afer using the updateusage flag.
Must I truncate and reload this table to reclaim the space.
(Doing a select into of the table to a new table does seem
to reclaim the space)
Thanks
LBHi Len
Take a look at DBCC CLEANTABLE and see if that helps.
It's documented in Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Len Bearse" <anonymous@.discussions.microsoft.com> wrote in message
news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> All
> We have a very large table (1M+ rows) in which we are
> storing large xmls (datalength(column) of 10K) in a text
> datatype field.
> We are using up space at a much greater rate than
> originally planned - and will stop saving the the xml
> for certain events. There are about 25 events during the
> lifetime of an order.
> I am trying to understand how we can get back the space
> allocated to this object if we decided to set the column => null. It does not seem to change the information returned
> by sp_spaceused even afer using the updateusage flag.
> Must I truncate and reload this table to reclaim the space.
> (Doing a select into of the table to a new table does seem
> to reclaim the space)
> Thanks
> LB
>|||It did not seem to help - please note in my tests
I am just setting the column = null.
>--Original Message--
>Hi Len
>Take a look at DBCC CLEANTABLE and see if that helps.
>It's documented in Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
in message
>news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> All
>> We have a very large table (1M+ rows) in which we are
>> storing large xmls (datalength(column) of 10K) in a text
>> datatype field.
>> We are using up space at a much greater rate than
>> originally planned - and will stop saving the the xml
>> for certain events. There are about 25 events during the
>> lifetime of an order.
>> I am trying to understand how we can get back the space
>> allocated to this object if we decided to set the
column =>> null. It does not seem to change the information
returned
>> by sp_spaceused even afer using the updateusage flag.
>> Must I truncate and reload this table to reclaim the
space.
>> (Doing a select into of the table to a new table does
seem
>> to reclaim the space)
>> Thanks
>> LB
>
>.
>|||Len
Ok, I see. You're not dropping the column, just setting it to null. But,
hey, that's a thought that might be a bit more efficient that a complete
recreate. Drop the column, run dbcc cleantable and then readd the column.
But of course, YMMV and you should test it well first.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:036801c3a577$66680450$a401280a@.phx.gbl...
> It did not seem to help - please note in my tests
> I am just setting the column = null.
>
> >--Original Message--
> >Hi Len
> >
> >Take a look at DBCC CLEANTABLE and see if that helps.
> >It's documented in Books Online.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> >> All
> >> We have a very large table (1M+ rows) in which we are
> >> storing large xmls (datalength(column) of 10K) in a text
> >> datatype field.
> >>
> >> We are using up space at a much greater rate than
> >> originally planned - and will stop saving the the xml
> >> for certain events. There are about 25 events during the
> >> lifetime of an order.
> >>
> >> I am trying to understand how we can get back the space
> >> allocated to this object if we decided to set the
> column => >> null. It does not seem to change the information
> returned
> >> by sp_spaceused even afer using the updateusage flag.
> >>
> >> Must I truncate and reload this table to reclaim the
> space.
> >> (Doing a select into of the table to a new table does
> seem
> >> to reclaim the space)
> >>
> >> Thanks
> >>
> >> LB
> >>
> >
> >
> >.
> >|||I guess the real underlying question is how is space
allocated to the structures the contain the lob data.
I had thought that the b-tree structure would allocate
data in a manner similar to other SQL server objects.
That as pages and extents were deallocated would be freed
and listed as available. It doesn't seem to be working
that way. In testing we don't seem to be freeing the data
to the same degree we are using it up.
I think what I am going to do one of the following
1) No check constraints - bcp table out - truncate table -
bcp table in - re-enable constraints
2) no check constraints - rename table to old_tbl - create
new_tab - insert into new empty table - re-enable
constraints
Len
>--Original Message--
>Len
>Ok, I see. You're not dropping the column, just setting
it to null. But,
>hey, that's a thought that might be a bit more efficient
that a complete
>recreate. Drop the column, run dbcc cleantable and then
readd the column.
>But of course, YMMV and you should test it well first.
>
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:036801c3a577$66680450$a401280a@.phx.gbl...
>> It did not seem to help - please note in my tests
>> I am just setting the column = null.
>>
>> >--Original Message--
>> >Hi Len
>> >
>> >Take a look at DBCC CLEANTABLE and see if that helps.
>> >It's documented in Books Online.
>> >
>> >--
>> >HTH
>> >--
>> >Kalen Delaney
>> >SQL Server MVP
>> >www.SolidQualityLearning.com
>> >
>> >
>> >"Len Bearse" <anonymous@.discussions.microsoft.com>
wrote
>> in message
>> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> >> All
>> >> We have a very large table (1M+ rows) in which we
are
>> >> storing large xmls (datalength(column) of 10K) in a
text
>> >> datatype field.
>> >>
>> >> We are using up space at a much greater rate than
>> >> originally planned - and will stop saving the the xml
>> >> for certain events. There are about 25 events during
the
>> >> lifetime of an order.
>> >>
>> >> I am trying to understand how we can get back the
space
>> >> allocated to this object if we decided to set the
>> column =>> >> null. It does not seem to change the information
>> returned
>> >> by sp_spaceused even afer using the updateusage flag.
>> >>
>> >> Must I truncate and reload this table to reclaim the
>> space.
>> >> (Doing a select into of the table to a new table does
>> seem
>> >> to reclaim the space)
>> >>
>> >> Thanks
>> >>
>> >> LB
>> >>
>> >
>> >
>> >.
>> >
>
>.
>