January 13, 2012 at 6:50 am
Yeah, the ghost records are definitely the problem.
Two things you can try (easier one first). Run a SELECT * FROM <table name> (must include LOB columns), see if that kicks the ghost cleanup into action (it will only realise there's stuff to cleanup when something reads those pages)
If that doesn't work, we'll try a harsher approach.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2012 at 7:00 am
GilaMonster (1/13/2012)
Yeah, the ghost records are definitely the problem.Two things you can try (easier one first). Run a SELECT * FROM <table name> (must include LOB columns), see if that kicks the ghost cleanup into action (it will only realise there's stuff to cleanup when something reads those pages)
If that doesn't work, we'll try a harsher approach.
I have tried the SELECT statement and then run Paul Randall's code to see when the GHOST CLEANUP last ran - the answer I'm getting is still 19th October!
One thing that I may not have mentioned - we have a scheduled maintenance window from 9am to 1pm tomorrow. By definition, there will be no Sessions running then, so we can do what we want to the Sessions table - TRUNCATE it, if necessary.
January 13, 2012 at 7:06 am
If nothing else fixes it, but that's a last solution, it's not really something you should need to.
One more try.
DBCC FORCEGHOSTCLEANUP
This is undocumented and not supported and shouldn't be used on production boxes, etc, etc. Might work, might not
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2012 at 8:03 am
DBCC FORCEGHOSTCLEANUP
Dear Gail,
I have scheduled this to run at 3am. If it doesn't work, then I will TRUNCATE the table during tomorrow's scheduled maintenance session.
Many thanks for taking such an active interest.
Best wishes
Mark
January 13, 2012 at 8:27 am
If you do decide to truncate, would you mind taking a backup of the DB first, intention being to restore it elsewhere (a test machine, or maybe your local desktop) and see if we can get to the bottom of this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2012 at 9:27 am
MarkThornton (1/13/2012)
Hi Perry,Thanks for helping. I ran Paul's code and got this result:
session_idstart_time status command
------------------------------------------------------------------
212011-10-19 10:20:51.610backgroundGHOST CLEANUP
This seems to imply that my GHOST CLEANUP process last run in October.
Could that be the root cause of my problem?
That's fine that means the task is running, there is a trace flag that turns it off but for you it's running.
It doesnt imply it last ran in Oct, that's going to coincide with the last time SQL Server was restarted 😉
Now, as Paul points out in his blog the cleanup starts up every few seconds and runs for a few seconds. In SQL Server 2008 it runs for longer. If it ran permanently performance would suffer!
An index rebuild should clear the pages according to Paul's testiment. There have been bugs around this in previous versions.
Check this link too.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 24, 2012 at 3:08 am
Dear Gail,
Sorry to be so long with an update, I have been away for a week on a training course.
Anyway, I largely have good news for you: I TRUNCATEd the offending table, and then scheduled the job that you suggested, i.e.
ALTER INDEX PrimaryKeyIndexName ON TableName
REORGANIZE WITH (LOB_COMPACTION = ON);
The table now grows to about 30MB during the day and then drops down to about 10MB overnight. If things stay like this, then this will be completely acceptable, but I will keep monitoring it for a while, just to make sure!
The only bad news is that we now have another table that is displaying similar characteristics. Like the original one, it has an NTEXT field which is being used to store far too much data - poor design, but outside my control. I will monitor that one too - but at least I now have a solution!
Many thanks for your help.
Best wishes
Mark
January 24, 2012 at 8:31 am
Glad to hear it's fixed, but I wish we'd been able to figure out why it was happening. This is not expected behaviour.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 10:40 am
MarkThornton (1/24/2012)
I TRUNCATEd the offending table
There was never any doubt this would work as the truncate will de allocate all the pages, no ghost records to clean up!
MarkThornton (1/24/2012)
and then scheduled the job that you suggested, i.e.ALTER INDEX PrimaryKeyIndexName ON TableName
REORGANIZE WITH (LOB_COMPACTION = ON);
Pointless as you just truncated\deleted the whole table.
MarkThornton (1/24/2012)
The only bad news is that we now have another table that is displaying similar characteristics. Like the original one, it has an NTEXT field which is being used to store far too much data
Follow Gails advice and restore a copy of this database to a test server. Delete a large amount of records from the table and leave the database for a period of time to allow the ghost cleanup to run fully. Remember it only removes a few pages at a time so could take some time to release a large amount of ghost records.
Utilise the link above to Paul Randals article to help you.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 6, 2014 at 4:42 am
I have experienced problems with tables containing LOB_DATA where non of the below statements had any effect in respect of releasing unused LOB_DATA pages.
alter table ... rebuild
alter index ... rebuild
alter index ... reorganize with (lob_compaction = on)
After expirementing I figured out that a way of compacting unused LOB_DATA pages is to
1. Add a new LOB column and update this colmn with non null values
2. Do a switch on the troubled column occupying LOB_DATA pages to be "NOT NULL"
3. Drop the new LOB column
4. Run DBCC CLEANTABLE on the table
Assume you have a table named PERSON with a NOT NULL text column named HISTORY. The statements to release LOB_DATA pages could look like this:
alter table PERSON
add DummyLOB text not null
constraint DF_EMPTYSTRING default CONVERT(text, '')
go
alter table PERSON alter column HISTORY text null
alter table PERSON alter column HISTORY text not null
go
alter table PERSON drop constraint DF_EMPTYSTRING, column DummyLOB
dbcc cleantable (0, 'PERSON', 10000)
In case your LOB column is nullable, you have to fill in the null values with for instance an empty string. You have to do so to be able to switch the column to be "NOT NULL". After the switch we will switch it back to be nullable and reestablish the null values. In this case the script could look like this (assuming the PK column of the tabel is named PERSONID):
alter table PERSON
add DummyLOB text not null
constraint DF_EMPTYSTRING default CONVERT(text, '')
go
select PERSONID into IDWITHNULL from PERSON where HISTORY is null
update PERSON set HISTORY = CONVERT(text, '') where HISTORY is null
alter table PERSON alter column HISTORY text not null
alter table PERSON alter column HISTORY text null
update PERSON set HISTORY = null
where PERSONID in (select PERSONID from IDWITHNULL)
drop table IDWITHNULL
go
alter table PERSON drop constraint DF_EMPTYSTRING, column DummyLOB
dbcc cleantable (0, 'PERSON', 10000)
go
My data files did not grow during execution of the scripts. Even in simple recovery mode some space was required for the log file.
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply