RE: How to make your SQL Server database grow exponentially with linear data

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Eric Cárdenas [MSFT] (ecardena_at_online.microsoft.com)
Date: 05/07/04 

Date: Fri, 07 May 2004 08:15:21 GMT

> We have this database that we use to collect 1000's of rows of data a day.
> Particularly in one table we collect about 1000 pieces of data a day in
the
> form of very small blobs (64k or so). Our database has been growing
> steadily from Nov 2003 with an initial size of 7gb to about 25gb on 10
April
> 2004. Not bad, seeing the amount of data we collect. Then from 10 April
to
> today (23 April) it grew to 65gb in about 2 weeks. What's going on? Did
> our input data increase exponentially? No, according to our logs data
have
> been arriving in a linear fashion. Maybe there's about 40gb of unused
space
> in the database? No, after we rebuild the indexes (using dbcc
updateusage)
> SQL Server reports no free space. Not possible you say?
>
>
> Well, turns out the problem are with the blob fields. Because we want to
> minimize the time it takes to search through the table, we wrote a service
> that takes 2 rows, adds the blobs together, then updates the one row with
> the resulting blob and deletes the other row. The service in other words
> doubles up the blobs. The resulting blobs are also doubled up and so
forth.
> This in itself should not be a problem because theoretically the new row
> should take up the same space (or even slightly less) than the original 2
> rows together. Wrong! What happens is when a blob gets bigger than 64k,
> SQL Server does not store the blob data with the row anymore, but moves it
> to a separate location. Now when you double up a blob and try to update
the
> row SQL Server realizes it can't store the blob in the same space as the
> previous one, so it allocates new space for it. So even though you aren't
> adding any new data to the database, the database file actually grows
> exponentially! Now some smaller blobs will eventually take up the old
space
> of a bigger blob, but they are much lower on the exponential curve than
the
> bigger blobs, so in total your database file grows near exponential with
> linear data. This can be quite a problem when your blob size gets to
about
> 50mb or so, and is the cause of our database growing so quickly in such a
> short time.
>
>
> Also, what worsens the problem is that it seems that SQL Server does not
> take these deallocated blob space into account when it calculates "unused"
> space in the database. Now if we deleted both rows in a double-up
operation
> and inserted a new row we probably would have been able to shrink the
> database periodically. But there does not seem to be anything we can do
to
> reclaim deallocated blob space Our aim is to stop the blobs growing when
> they reach a certain size and archive them off. Has anybody experienced
the
> same problem, or are there any suggestions as to how we can reclaim (or
> avoid) this "unused" space?
>
> Thanks,
>
> Pieter
--------------------
Hi Pieter,

Have you tried DBCC ShrinkDB and ShrinkFile?

Hope this helps, 

--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Relevant Pages

  • Re: How to make your SQL Server database grow exponentially with linear data
    ... Turns out it's a bug in SQL Server, ... database, which means there was only about 17gb of actual data in the ... separate data file, so when it gets out of control again we can just move ... turns out the problem are with the blob fields. ...
    (microsoft.public.sqlserver.server)
  • Re: manipulating blobs in sqlplus
    ... SQL Server). ... There is also no size limit on the blob field ... site outside of the database and have a script load them in. ... 15 returning theBlob into l_blob; ...
    (comp.databases.oracle.server)
  • How to make your SQL Server database grow exponentially with linear data
    ... We have this database that we use to collect 1000's of rows of data a day. ... SQL Server reports no free space. ... turns out the problem are with the blob fields. ... of a bigger blob, but they are much lower on the exponential curve than the ...
    (microsoft.public.sqlserver.server)
  • Re: Blobs and SQL Server
    ... Older versions of SQL Server required the blob be loaded in chunks, ... There is a cpu and disk IO hit while inserting the blob. ... Director of Text Mining and Database Strategy ... If a large file is inserted, then it will require many small chunks. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Hello: not so much php, more like HTML..?
    ... Jerry would tell you to just serve them up straight from the database, and forget about the filesystem, I'm not so sure:). ... I suspect the answer is that for n files, us the pth root of n as the number of subdirs, where p is the depth of the subdirs...but a lot depends on caching algorithms in the directories. ... I shudder to think how long adding slashes to a 60Mbyte binary image might take, or indeed how much virtual memory holding it in a 60Mbyte php string might use.. ... Anyone ever handled BLOB objects that large, ...
    (comp.lang.php)