January 12, 2012 at 4:32 am
Does it will make any seance to change datatype ntext to NVARCHAR.
January 12, 2012 at 4:41 am
It would make perfect sense, but it is outside my organisation's control.
January 12, 2012 at 4:50 am
Truncate of table can resolve the problem, might be follow these step to perform this task-
STEP.1 - CREATE TRIGGER on BEFORE INSERT event to get table count and if count=0 then TRUNCATE the table.
STEP.2 Make trigger disable at day time and enable this only at night time through SQL Job.
Test above for one day and check the table space.
January 12, 2012 at 6:23 am
MarkThornton (1/12/2012)
My current theory is that this isn't working, due to the presence of an ntext field in the table.
A ntext column won't prevent the index from rebuilding but, as I said, rebuild doesn't affect the LOB columns. Try the other index command that I gave you, and please don't try random stuff as it's unlikely to help.
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 12, 2012 at 6:40 am
Gail,
Many thanks for your help and advice.... just to confirm, the command you were suggesting was
ALTER INDEX IndexName ON TableName
REORGANIZE WITH (LOB_COMPACTION = ON)
Have I got this right?
Best wishes
Mark
January 12, 2012 at 6:46 am
Yup. Run it once and see if it does have the effect that you want (run it on the clustered index). If that does help then, depending how often you do those deletes you may want to schedule it daily or weekly (in addition to your normal index maintenance)
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 12, 2012 at 6:49 am
I am in the process of scheduling this as a one-off job tonight. Assuming it works, I will then schedule it as a weekly job.
Many thanks for your help, I'm now in a much calmer frame of mind than I was this morning!
Best wishes
Mark
January 12, 2012 at 6:59 am
Cool. Will you post back tomorrow and let us know about the size of the table, the amount of free space in the database (sp_spaceused) and the like?
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 12, 2012 at 7:25 am
I hope to - but please don't take that as a promise. I am away all next week so tomorrow will be a very busy day!
January 12, 2012 at 7:27 am
Cool. Post back when you can so that we can help further if necessary and make sure that the problem really is fixed.
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 3:36 am
Bad news.... last night I ran the following two jobs:
DBCC CLEANTABLE (0, SessionsTableName, 0) WITH NO_INFOMSGS;
ALTER INDEX ClusteredIndexName ON TableName REORGANIZE WITH (LOB_COMPACTION = ON)
The jobs ran without errors, but no space was reclaimed from the Sessions table.
Last time we had this problem, I waited until we had a scheduled maintenance period, when we had no Sessions on the system, and used that opportunity to truncate the Sessions table. But I would like to find a better solution than that!
All help gratefully received.
January 13, 2012 at 4:46 am
this is a known issue, truncate clears the table as all pages are de allocated. When deletes occur the ghost delete process fires to remove pages, with LOBs this takes some time. Paul Randal blogged this and there is a query he provided at this link to see when the ghost removal process fires up. The link also details how to go and verify that you have ghost records that require removing.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 13, 2012 at 5:03 am
Could you run this and post the results (attached as an excel spreadsheet will be easiest)
SELECT ddips.index_id, index_level, alloc_unit_type_desc, avg_fragmentation_in_percent, avg_page_space_used_in_percent, ghost_record_count, avg_record_size_in_bytes, forwarded_record_count, record_count, page_count
, au.data_pages, au.total_pages, au.used_pages
FROM sys.dm_db_index_physical_stats(<database id>, <object id>, NULL, NULL, 'detailed') AS ddips
INNER JOIN sys.partitions AS p ON ddips.object_id = p.object_id AND ddips.index_id = p.index_id
INNER JOIN sys.allocation_units au ON au.container_id = CASE type WHEN 2 THEN p.partition_id ELSE p.hobt_id END AND au.type_desc = ddips.alloc_unit_type_desc COLLATE database_default
Replace <database id> and <object id> with the IDs of the database and object in question.
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 6:38 am
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?
January 13, 2012 at 6:46 am
Dear Gail,
I have run your code and attach an Excel file, as you requested.
Best wishes
Mark
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply