Is it as simple as just giving rid of the ‘with clean up’ or does it need a rewrite as the link suggests as it is a fire and forget...

Is it as simple as just giving rid of the ‘with clean up’ or does it need a rewrite as the link suggests as it is a fire and forget...

It is then ended by the target with

Cursor deallocated.

END

IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

BEGIN

END CONVERSATION @dh WITH CLEANUP

END

Not exactly my code but it is a set up like this

DECLARE @dh UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dh

FROM SERVICE [Initiator]

...

SB waittype is either sleep_task or Broker_Flush. This would indicate to me that it is flushing in memory to tempdb. Hence my problem. How do I stop this

Thanks for your posts, can I get back to you mid next week with the SB setup.

I can see that I am getting the http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog

Message returned, the sproc...

If you are referring to how are the queues dealt with, they are processed via a sproc. Which does have logic to capture 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'and end the converstaion with cleanup....

Correction they are both appearing in this query, and can hang about for between 30 mins to 1 hour.

Additional info-

Alloc_pages in tempdb keeps growing with dealloc = 0 from...

Thanks, I'm getting closer to finding the issue.

When I execute

select * from sys.dm_db_task_space_usage

where internal_objects_alloc_page_count <> 0

Session 10 and 14 have a very high number of internal_objects.

Activity monitor show system...

How do I find out what is causing the Internal_objects size to grow in tempdb.

I have dbcc running each night and update stats runs each night. I would expect tempdb...

Nice. Will try it tomorrow. Just what i was looking for.

Is this for a production db. If so I wouldn't shrink the db and there are considerations to take into account when shrinkung the logfile many people advise not to....

Additionally , each file was set to 4gb, would it be worth extending this to 5gb and turning off autogrowth.

I'm having the same issue, anybody.

SELECT sys.objects.name

, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact

, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '')...