locked
VarBinary(Max) SQL Server RRS feed

  • Question

  • We have an odd situation happening with one of our tables in our database (sql server 2012 Sp1) and I am hoping someone can shed some light on it. This table has 3 columns id(bigint), timestamp(datetime),photo(varbinary(max). This table is part of transactional replication so it also exists in our history database (apart from operational). Both databases are on the same server. We have a nightly clean-up job that removes old data from both operational and history database. This table never has more than few thousand records (in operational database) during the day. Once the clean-up finishes and indexes are rebuild, the unused space from this never gets returned to OS. Next day when new data comes in, instead of using that unused space, table grows in size. When we look at the free space available on the data files we don't see anything available (not that we shrink our data files but just to give an idea). We have run cleantable command couple of times to reclaim this unused space but is this the best way to recover space? Our concern is that if we don't run cleantable command on some type of a schedule, no matter how much free space we have on the hard drive it will always fill up. Any thought or suggestions will be appreciated.
    Monday, May 16, 2016 6:47 PM

Answers

  • I worked with a client that a table that filled up daily with LOB data, and at the end of the day, most of the rows were archived. Their problem was that the process was too slow, but the slowness was due to all the LOB data being scattered around.

    Now, in their case, they were using the old LOB types (text/ntext/image) and the main bulk of the LOBs were small enough to be in row, so changing to "text in row" solved their problem. You are already a good boy and using varbinary(MAX) which is stored in-row for small LOBs. Then again, if your LOBs are generally larger than 8KB that will not help.

    But if DBCC CLEANTABLE works for you, you have a solution.

    Tuesday, May 17, 2016 9:17 PM

All replies

  •  Is this a heap table ?

    I seem to remember this being the case for heap tables.


    Hope it Helps!!

    Monday, May 16, 2016 7:00 PM
  • No, the table has a clustered index on ID column.
    Monday, May 16, 2016 7:11 PM
  • Try using ALTER INDEX WITH REORGANIZE on the table. In difference to rebuild, this operation includes LOB compaction, I would guess that your problem is with the LOB data.

    Monday, May 16, 2016 9:42 PM
  • We have run cleantable command couple of times to reclaim this unused space

    DBCC CLEANTABLE (Transact-SQL) releases space only when you have dropped a column, but not after only deleting data.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 17, 2016 8:28 AM
  • I agree however DBCC Cleantable command seemed to have worked for us and we are able to reclaim space held up by the table. The structure of the table does not change so I am also a little surprised as to how this has worked.
    Tuesday, May 17, 2016 1:41 PM
  • There were issues with the ghost cleanup process.  Please post the results of SELECT @@VERSION.

    Also look at the "troubleshooting" section and see if there are ghost records in your table:

    https://blogs.msdn.microsoft.com/sqljourney/2012/07/27/an-in-depth-look-at-ghost-records-in-sql-server/

    Tuesday, May 17, 2016 1:58 PM
  • Thank you for pointing to the blog Tom. I am going to test this out and post my findings. In the mean time here is the version detail.

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

    May 14 2014 18:34:29

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    Tuesday, May 17, 2016 5:21 PM
  • I would suggest you install SP3 before spending too much time on this problem. You are running a pretty old version, and although I don't have any specific knowledge of anything which would fix this issue, it may already be fixed.

    https://support.microsoft.com/en-us/kb/321185#bookmark-sqlserver2012

    Tuesday, May 17, 2016 5:32 PM
  • I agree that this instance should be patched to the latest SP however having any information which says that the new SP will fix the issue will help in convincing our customer for it. These systems at the customer run 24/7 so patches and upgrades are not scheduled as easily as an in house system. However we will try and get the latest SP in.

    But there is still something odd about this issue. The historical database has no problems on this particular table. It has 5 times more data than publisher version of this database and 3 times less space used by this table on the publisher version. So its confusing to see that this table is not releasing space in one database and works fine on the other database.

    Here is what i see in my test environment when i run sp_spaceused

    TABLE_WITH_ISSUE      0                  171872 KB 166872 KB 0 KB 5000 KB

    Select * from sys.dm_db_index_physical_stats(db_id('MY_DB'),272720024,NULL,NULL,'DETAILED')

    gives me 0 ghost records

    1 Clustered Index is on ID

    1 NonClustered Index is on Timestamp

    Once I run DBCC CLEANTABLE on this table this is the result:

    TABLE_WITH_ISSUE 0                    32 KB 32 KB 0 KB 0 KB

    Tuesday, May 17, 2016 6:59 PM
  • I worked with a client that a table that filled up daily with LOB data, and at the end of the day, most of the rows were archived. Their problem was that the process was too slow, but the slowness was due to all the LOB data being scattered around.

    Now, in their case, they were using the old LOB types (text/ntext/image) and the main bulk of the LOBs were small enough to be in row, so changing to "text in row" solved their problem. You are already a good boy and using varbinary(MAX) which is stored in-row for small LOBs. Then again, if your LOBs are generally larger than 8KB that will not help.

    But if DBCC CLEANTABLE works for you, you have a solution.

    Tuesday, May 17, 2016 9:17 PM