Hi Perry

The results are below

dboCommitmentsForPostingpk_CommitmentsForPosting10PRIMARY11660979NONELOB_DATA258517230

dboCommitmentsForPostingpk_CommitmentsForPosting10PRIMARY11660979NONEIN_ROW_DATA909090

for reference, I've also included the results for the SPS_TEST table which is the one I populated as a copy of the table above.

dboSPS_TESTpk_CommitmentsForPosting110PRIMARY11660979NONELOB_DATA1691690

dboSPS_TESTpk_CommitmentsForPosting110PRIMARY11660979NONEIN_ROW_DATA909090

To me, this...

No Problem.

thanks for the ideas.

sorry, I should have clarified

I ran the following:

ALTER INDEX [pk_CommitmentsForPosting]

ON dbo.CommitmentsForPosting

REORGANIZE with (LOB_COMPACTION = ON)

That is the only index on the table an has a definition as per below

ALTER...

Just re-ran the index re-organisation, and it made no difference.

Presumably, this is because the ntext column isn't included in the index.

Hi,

I knew I'd forgotten to mention something.

I tried that just before I decided to rebuild the index.

I think it is the lob size that is the problem.

If I query sys.dm_db_partition_stats,...

Hi Alan,

Thank you very much for the reply.

Unfortunately, it doesn't really help me in my specific case, but has given me an idea which has helped me deal with another...

I don't know if I should be starting a new post for this or not.

When I've been testing the performance of my drop table statements, I've been restarting the SQL...

Hi Kurt,

Thank you very much for the input and ideas.

There are no hard and fast goals that I have to meet regarding the performance.

This process will still run out of...

Hi,

thanks again for the replies.

At the moment, the process we have for dropping these tables does indeed run out of hours.

However, I've been tasked with rewriting it for a number...

Thank you all very much for your replies, and thank you Jeff for the excellent article that Alan linked to earlier.

I had often read about the merits of set based...

Hi Alan,

Thank you for the reply.

I'll try that code and see how it performs against my other methods.

With regards to the temporary tables within the application, I accidentally used the...

Thank you all for your replies.

It confirms exactly what I thought.

My hands are tied a bit as someone else provides support for their SQL Server, but now that I'm confident...

Hi,

Thanks for the replies.

I appreciate that it all comes down to the amount of data loss that is acceptable.

I suppose the bit that I'm struggling to understand is why they...

Hi GSquared,

Thank you very much for your assistance with this.

I do have a network admin that I can liaise with, but any changes to the SSRS server will be down...

Thank you very much for your help.

It appears that overnight it has sorted itself out.

I now have an index_size similar to the size of the data.

The unused size is still...