Re: reduce physical memory usage by updating ntext field

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/09/05 

Date: Sun, 9 Jan 2005 13:01:59 -0500

Relations do make this more difficult for sure. 2005 will solve this
though. 

-- 
Andrew J. Kelly  SQL MVP
"Shai Goldberg" <ShaiGoldberg@discussions.microsoft.com> wrote in message 
news:231384BE-9409-440D-8643-FDE01DCAC640@microsoft.com...
> Well,
>
> This is surely not what I have expexted to here, but I understand that 
> there
> is nothing to do!
> The problem with BCP is that this table has two foreign keys that other
> tables depens on and BCP will disconnect the foreign key connection.
>
> "Andrew J. Kelly" wrote:
>
>> Even though the size of the data in the text columns is smaller the page
>> allocations are the same.  Reindexing does not help either.  The best way 
>> at
>> the present time to reduce the size of the page allocations is to BCP out
>> all the data, truncate the table and bcp it back in.  SQL2005 will allow 
>> you
>> to clean up blobs but 2000 is terrible at it.
>>
>> -- 
>> Andrew J. Kelly  SQL MVP
>>
>>
>> "Shai Goldberg" <Shai Goldberg@discussions.microsoft.com> wrote in 
>> message
>> news:5A8EE3E4-F4B0-4ECD-A471-115A35BA392B@microsoft.com...
>> > Hi,
>> >
>> > I have 5 ntext fields that have 40k value string each for every record
>> > The table has 23,000 records so the table occupies about 3G of data.
>> > So I tried to changed values inside the ntext fields to be a string 
>> > such
>> > as
>> > 'Deleted' in order to free some memory, this data in no longer needed.
>> > I have used Update, WriteText and also tried to use UpdateText to 
>> > delete
>> > the
>> > text so I'll be able to shrink the size of database.
>> > No success on this side, the DataLength issmaller but the database 
>> > won't
>> > shrink at all.
>> > When deleting the table content using 'Delete Table <TableName>' 
>> > statement
>> > the ntext fields are shrinked but there memory is not freed.
>> > Is there any one who can help me? I want to reduce size of data length 
>> > of
>> > ntext but without deleting all data in the table, is there a way to do
>> > this?
>> >
>> > Thanks,
>> >
>>
>>
>>

Relevant Pages

  • Re: index fragmentation
    ... > Andrew - can you provide a link to Tibor's ... > 2) shrink has run and totally reversed the fragmentation status - to be the ... >>>>> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.server)
  • Re: huge log file when inserting data
    ... >> The only way to flush that log is to detach the database ... >>>> Log files do not shrink on their own, ... >>>> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Help! Maintainence paln fails
    ... the next time the maintenance plan goes to "Optimize" the indexes ... Andrew J. Kelly SQL MVP ... >> situation because you specified in the plan to shrink the data ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.server)
  • Re: how to use bcp utility..
    ... BCP is not the tool for bringing in a mdf. ... Andrew J. Kelly SQL MVP ... > sql2k on nt5. ...
    (microsoft.public.sqlserver.server)
  • Re: Shrinking the index file group
    ... Andrew J. Kelly SQL MVP ... It is SHRINKDATABASE that will never shrink any file smaller than ... >> DBCC SHRINKFILE instead to ensure you are getting what you want. ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.programming)