We have a table in a production system that (for outdated reasons) is running SQL 2005 (9.0.5266) and contains a TEXT column (along with several other columns of various data types).
Suddenly (starting from a week ago) we noticed that the size of this table grows linearly by 10-15 GB per day (whereas earlier it always remained unchanged). The table is a queue for the messaging system, and therefore the data in it is completely updated every few seconds. At any given time, there can be from 0 to about 1000 lines, but it changes quickly when messages are inserted and sent (after which they are deleted).
We cannot find anything that was changed the day the growth began, so there is no obvious potential reason at this stage.
One “obvious” culprit is the TEXT column, so we checked to see if any massive values were saved, but (using DATALENGTH) we did not find a single line above ~ 32k. We launched CHECKDB, updated space usage, rebuild all indexes, etc. - nothing reduces the size (and CHECKDB did not detect errors).
We requested sys.allocation_units, and the increase in size is definitely LOB_DATA (which shows total_pages and used_pages increasing with constant speed).
, , ( , ), . , , , , .
, 14 ( ), ~ 500 , MAX (DATALENGTH (text_column)) 35k.
, "" , - , , , ?
Cheers,
Dave