LOB data keeps growing and growing...

  • sqlnyc

    SSCommitted

    Points: 1726

    Hi everyone --

    Would really appreciate any feedback on this problem.

    First I wanted to say that none of what I describe here is my design.....

    I have a table that resides on its own filegroup that contains an image column. This column contains compressed (7zip) versions of data from several tables that pertain to client transactions. The table is purged daily based on CreationDate and an Active flag (DDL is at the bottom of this post).

    Since July, I've seen much higher growth for this file, but so far am unable to explain why.

    I track growth daily, and here are some of the stats:

    row_count 76029

    reserved 133078048 (133.07GB on 2014-04-26)

    data 133076112

    index_size 1760

    unused 176

    rundate 2014-04-26

    row_count 76099

    reserved 154606576 (154.6GB on 2014-09-18)

    data 154599544

    index_size 3576

    unused 3456

    rundate 2014-09-18

    Rowcount is about the same, and so I'm somewhat at a loss to explain what the growth is attributed to.

    Thinking that this might be due to space not being reclaimed when rows were deleted, after restoring the db on a test server, I did the following:

    1. created a new temporary filegroup

    2. made the new file/filegroup the default

    3. copied the table to this new file group via SELECT INTO

    4. verified the size of the new table (it was not any different)

    Thanks in advance for any feedback on this issue.

    Best wishes to all,

    sqlnyc

    CREATE TABLE [dbo].[CompressedDataStore]

    (

    [PId] [INT] IDENTITY(1, 1) NOT NULL

    ,[Id] [INT] NOT NULL

    ,[CompressedData] [IMAGE] NULL

    ,[CreationDate] [DATETIME] NOT NULL

    ,[Active] [BIT] NOT NULL

    ,[PGUID] [UNIQUEIDENTIFIER] NOT NULL

    ,CONSTRAINT [PK_CompressedDataStore] PRIMARY KEY CLUSTERED ( [PId] ASC )

    WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100 )

    ON [CompressedDataStore]

    ,CONSTRAINT [UQ_CompressedDataStore_PGUID] UNIQUE NONCLUSTERED

    ( [PGUID] ASC )

    WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100 )

    ON [CompressedDataStore]

    )

    ON [CompressedDataStore] TEXTIMAGE_ON [CompressedDataStoreSeparateFile]

    ALTER TABLE [dbo].[CompressedDataStore] ADD CONSTRAINT [DF_CompressedDataStore_Active] DEFAULT ((1)) FOR [Active]

    ALTER TABLE [dbo].[CompressedDataStore] ADD CONSTRAINT [DF_CompressedDataStore_PGUID] DEFAULT (NEWSEQUENTIALID()) FOR [PGUID]

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    The ghost cleanup should get 100 records at a time after delete, but I'd think over days you'd be fine.

    This talks a bit about that process and tracking: http://social.msdn.microsoft.com/forums/sqlserver/en-US/2dc3a15b-684f-4d8e-acaa-4d815ef13543/sql-2008-image-data-type-freeing-db-space

    I might refactor this table to use varchar(max) instead of image. I don't think that image -> varchar(max) should affect your app, and I'm not completely sure of the underlying handling, but I know that work has been done to varchar and not image to make it more efficient (in v out of row). Not sure if that's back ported or if image/text get mapped to varchar(max).

    I wonder if you've hit some bug. Are you just issuing a

    delete CompressedDataStore

    where creationdate < datediff()

    I'd expect this to release space after hours, if not minutes.

    Have you tried an index rebuild on this table?

  • sqlnyc

    SSCommitted

    Points: 1726

    Hi Steve,

    Thanks very much for your reply.

    I will research the link you posted.

    Long ago, when this server was running SQL 2008R2 RTM, there was indeed an issue with ghost cleanup. But now we are running SQL 2008R2 SP2, which along the way had a number of bug fixes related to the ghost cleanup process. I will verify again to make sure this not the issue.

    The delete is something like:

    delete CompressedDataStore

    where (CreationDate < @InActiveRecordsDate )

    OR (Active = 0 AND CreationDate < @ActiveRecordsDate )

    When I copied the table to a new file group, I did an index rebuild, but of course that doesn't touch any of the LOB data.

    sys.allocation_units returns:

    type_desc LOB_DATA

    total_pages 19321790

    used_pages 19321607

    data_pages 0

    The image column stores compressed versions of client records, using some zip library (not my design). If a client needs to roll back to a previous version of their data, this archive is decompressed and voila. I'm not sure that varchar(max) could/would handle this, but also doubt management will change without extremely dire circumstances, as this process is one of the cornerstones of the system.

    Thanks,

    sqlnyc

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    Sorry I don't have more. The LOB stuff is a bit of magic at times, since it's so poorly documented.

    I'm suspecting that something is holding onto allocations for the table, for some reason. No indexing of the LOB column? Does the table have a PK?

    I might email Paul at SQLSkills and ask him, maybe see if he will comment here or write a blog on what's happening. Can you repro this easily?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    Look at this as well, see if there's something you can decode from your system using a few of Kimberly's scripts: http://www.sqlskills.com/blogs/kimberly/understanding-lob-data-20082008r2-2012/

  • ScottPletcher

    SSC Guru

    Points: 98120

    You did add 70 rows, so while the growth is a little higher than average, it's not ridiculously high. Yes, you do want to convert to varbinary(max) instead of image when you can, but that's not likely critical either.

    You could run sp_clean_db_file_free_space to insure that all ghost space on that file has been cleaned up.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • sqlnyc

    SSCommitted

    Points: 1726

    Hi Scott and Steve - thanks very much for your replies.

    I've emailed Paul Randal about this in the past.

    His reply was:

    "The issue is that LOB extents are not deallocated or compacted in any way by the DELETE operations, so your backups have been backing up extents with unused (but full of non-compressible bytes) pages in them. With the operations you did, you created new, compact LOB storage with no free, unused pages in them. Hence the drop in size."

    This was specifically related to a question I asked him about why a zip of a BAK might be smaller after doing the copy-to-temp-table steps I outlined earlier. I will research this further, but as you mentioned Steve, there's not tons of info about this on the web.

    Will report back to this thread with my findings.

    Gents - thanks again for your efforts.

    sqlnyc

  • sqlnyc

    SSCommitted

    Points: 1726

    Just found this, which may be viable, but is sort of a brute force way to do it:

    http://itknowledgeexchange.techtarget.com/sql-server/deleting-lob-data-and-shrinking-the-database/

    sqlnyc

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply