December 13, 2010 at 11:17 am
Hi,
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
GO
CREATE TABLE [dbo].[T_Test](
[KeyHash] [nvarchar](50) 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 INDEXLOB_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
April 29, 2011 at 9:08 am
I have the same situation.
Moreover:
DBCC cleantable
DBCC UPDATEUSAGE
DBCC FORCEGHOSTCLEANUP (undocumented)
EXEC dbo.sp_clean_db_free_space
not help.
The only thing, that sometimes helps - stop sql server process and start again. During DB startut it cleans all the ghosts from LOB_DATA.
Table has the 'large value types out of row' option set.
Anybody!?
Restarting production server under heavy load is not an option!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply