SQL Server 2008: Table size is increased due to ghost records related to LOB data

A have the following table on Production environment. It is heavily updated (lots of inserts and deletes). This table contains LOB data types - ntext and nvarchar(max). Data is constantly removed and inserted in this table. But total row count is quite stable and is about 150,000.

But for unknown reason table size is only increased. It means that space of deleted data is not release.

For example, at this moment there are 150,000 rows in the table and it occupies about 60GB. If I copy this data to new table (simple insert into) then my data will occupy only 10GB.

What I tried to do:

1. Shrink file or database is not helping me
2. Index rebuild is not helping me
3. DBCC CLEANTABLE is not helping me

Here's the table structure:

CREATE TABLE dbo.T_Test(
KeyHash nvarchar(5开发者_开发知识库0) NOT NULL,
SiteDomainId int NOT NULL,
srcFullUrl nvarchar(max) NOT NULL,
srcResponse ntext NOT NULL,
srcExpirationDate datetime NOT NULL,
srcKey nvarchar(max) NOT NULL,
srcCachePeriodInMinutes int NOT NULL,
srcNumOfHits int NOT NULL,
srcVital bit NOT NULL,
CONSTRAINT PK_T_Test_1 PRIMARY KEY NONCLUSTERED
(
KeyHash ASC,
SiteDomainId ASC
))
GO
CREATE CLUSTERED INDEX [IX_T_Test_srcExpirationDate_ppa] ON dbo.T_Test
(
srcExpirationDate ASC
)
GO

What I know exactly that the issue is in the ghost records related to LOB data. select * from sys.dm_db_index_physical_stats(db_id(), object_id('MyTable'), null, null, N'DETAILED') returned the following:

index_type_desc alloc_unit_type_desc record_count ghost_record_count
CLUSTERED INDEX LOB_DATA 394996 2869376

But ghost process is working normally, i.e. ghost records are removed for IN_ROW_DATA of clustered index.

At this moment I don't have idea how to delete ghost records and reclaim space. The only way is to truncate table and upload data again.

Any suggestion how to avoid this issue are valuable. Thank you.

Configuration of my environment is Microsoft SQL Server Web Edition (64-bit) 10.0.2531.0

Could be the ghost clean up never catches up with DELETEs (on SF from Paul Randal) especially given your usage pattern. I remember seeing this on SF but I've never had this issue and have never tried Paul's suggested fix so YMMV sorry.

"Could be the ghost clean up never catches up with DELETEs (on SF from Paul Randal) especially given your usage pattern. I remember seeing this on SF but I've never had this issue and have never tried Paul's suggested fix so YMMV sorry."

I have read Paul Randal approach. And it really works but only for IN_ROW_DATA. In my case ghost records for IN_ROW_DATA are cleaned, but ghost records for LOB are not cleaned. I ran on Production loop with constant table scan. Ghost records for IN_ROW_DATA are close to zero for this case, but it didn't change behaviour of LOB ghost records.

I have restarted sql server process and the issue has been resolved.

See cumulative update 4 for SQL 2008 R2 SP1

http://support.microsoft.com/kb/2622823