Hi,
We are using service broker between two different instances. But were facing issues with increasing row count in conversation_endpoints view. We found that this was because we were using default value for lifetime for the conversation which is value of size int. Later on we changed the lifetime to 1 minute and conversation_endpoints view start getting cleaned up after 30 minutes
Following commands are used to send message
Before :
BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);
END CONVERSATION @.handle;
After:
BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
WITH LIFETIME = @.lifetime;
SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);
END CONVERSATION @.handle;
But as we use default life time for a long due to which around 15 million records got acumlated in this view. What is the best way to clean up this view.
END Conversation @.handle with cleanup is taking so long is their any other way to do this
Thanks,
Prashant
Not sure if this is the total cause of your problem but ending a conversation before the first message is sent on it tends to leave it in an unstable state. Remus has a good explanation here: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx|||
Thanks for information. But as of now i want to know what is the best way do cleanup on sys.conversation_endpoints with 14 million rows
|||I see - not interested in doing it right but doing it wrong faster.
I assume you have a script that loads all the dialog handles into a cursor and then calls END DIAlOG WITH CLEANUP on each one. If you want your application to continue working while you are cleaning up then that's the only way. If you can shut down your applicationso that there are no dialogs or messages that you care about then an ALTER DATABASE command with the SET NEW_BROKER parameter will blow away all traces of any dialogs and messages.
|||If you moved to SP2, you can do ALTER DATABASE ... SET NEW_BROKER, it will truncate every relevant internal table (conversation_endpoints, conversation_groups, transmission_queue and all message queues).
Do not attempt this with pre-SP2 SQL 20005 because it will literaly do 14 mil END CONVERSATION ... WITH CLEANUP in one transaction lasting forever.
Note that NEW_BROKER will nuke every conversation, including currently active ones. If you cannot afford this, then you must END conversations individually, if you batch commit it doesn't take that long actually.
|||Thanks for the instant replies. Unfortunately we cannot shutdown the application here. So i think only option left is using WITH CLEANUP. But this might be a help in sometime in future. Thanks again for wonderful upport
|||In that case, be sure to use Remus' suggestion of batch commits. END a few hundred conversations and then commit the transaction. This is much more efficient than doing each END CONVERSATION in its own transaction.Hi Rafael,
I need to create a new excel file daily and then need to mail it.
The problem iam facing is that ,when i schedule a job the task that dumps data into excel(Dataflow task ) gives a validation error.Everytime i need to manually link the sheets of the excel to the corresponding tables.
Any idea why this is happening?So iam unable to schedule as a job...
Thanks,
Vani.
This is more of an integration services forum question than data access forum question. Moving this thread to SQL IS forums.|||You might try setting the DelayValidation property true on the data flow task and the connection manager. You might take a look at this post for more information on dynamically creating Excel files in SSIS.
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html
