I am ending conversations properly for a dialog, the end conversation at the target properly marks the conversation as closed in sys.conversation_endpoints and sends the EndDialog message to the initiator.
The initiator get the EndDialog message from the target and ends the conversation and it clears out of sys.conversation_endpoints.
I know that to avoid reply attacks that the conversation at the target will only clear out in 30 minutes.
However it has been a day now and the conversation is still sitting in sys.conversation_endpoints as CLOSED at the target.
How do i troubleshoot this ? Why is the conversation not clearing?
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
I already had a thread for same problem but didn't recieved any response in last 4-5 months so I created a new thread for this
I am using service broker in between two database servers. following is the way i am sending and receiving messages
Send
BEGIN TRAN BEGIN DIALOG CONVERSATION @handle FROM SERVICE @SendService TO SERVICE @ReceiveService ON CONTRACT @Contract WITH LIFETIME = @lifetime;
SEND ON CONVERSATION @handle MESSAGE TYPE @xmlMessageType(@xmlMessage); COMMIT
Receive
BEGIN TRAN; RECEIVE TOP(1) @xmlMessage = message_body, @handle = conversation_handle, @message_type = message_type_name FROM TransactionQueue;
---------------------------------------------------------------------------------------------------- -- Check to make sure a message was returned to process. In theory this should never happen. ---------------------------------------------------------------------------------------------------- IF @@rowcount = 1 BEGIN
IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN END CONVERSATION @handle; COMMIT RETURN 0 END
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN RAISERROR(N'Received error %s from service [Target]', 10, 1) END CONVERSATION @handle; COMMIT RETURN 0 END
SET @sql = 'EXEC '+@message_processor_name+' @xml'
BEGIN TRAN EXEC sp_executesql @sql, N'@xml XML', @xml=@xmlMessage COMMIT TRAN END CONVERSATION @handle; END COMMIT
I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.
1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.
2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.
Hi all, i searched everywhere but couldn't find any info on the following error that i'm currently receiving:
"The conversation endpoint is not in a valid state for SEND. The current endpoint state is 'DI'."
I understand that this is due to some problem in my send/receive protocol but how do i fix this problem so that i can continue with my testing? Right now i'm forced to drop my entire test database and reinstall everytime this message shows up because i can't send/receive any messages at that point. Is there anyway to get rid of it?
Is there any way to ensure that messages sent on different dialogs have the same conversation group id on the target queue? I was attempting to set the conversation group id on the dialog before sending but learned that this only works on the initator end.
I have messages that could be sent from different applications (and at slightly different times) that need to processed exclusively (i.e. have the same conversation group id).
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005 Two databases Two end points - 1 in each database Two stored procedures: SP1 is activated when a message enters the sending queue. it insert a new row in a table SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue. I know that SP1 and SP2 are behaving properly because i get the expected result. Sp1 is inserteding the expected data in the table SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs. error #1 The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2 The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
My service broker was working perfectly fine earlier. As I was testing...I recreated the whole service broker once again.
Now I am able to get the message at the server end from intiator. When trying to send message from my server to the intiator it gives this error in sql profiler.
broker:message undeliverable: This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'.
broker:message undeliverable This message could not be delivered because the 'receive sequenced message' action cannot be performed in the 'ERROR' state.
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block DECLARE @conversationHandle UNIQUEIDENTIFIER, @err int, @counter int, @DialogTimeOut int, @Message nvarchar(max), @SendType int, @ConversationID uniqueidentifier select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :) select @DialogTimeOut = Value from dbo.tConfiguration with (nolock) where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec' WHILE (1=1) BEGIN -- Lookup the current SPIDs handle SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock) WHERE spid = @@SPID and messagetype = 'TestQueueMsg'; IF @conversationHandle IS NULL BEGIN BEGIN DIALOG CONVERSATION @conversationHandle FROM SERVICE [InitiatorQueue_SER] TO SERVICE 'ReceiveTestQueue_SER' ON CONTRACT [TestQueueMsg_CON] WITH ENCRYPTION = OFF; BEGIN CONVERSATION TIMER ( @conversationHandle ) TIMEOUT = @DialogTimeOut -- insert the conversation in the association table INSERT INTO tConversationSPID ([spid], MessageType,[handle]) VALUES (@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [TestQueueMsg] (@Message)
END ELSE IF @conversationHandle IS NOT NULL BEGIN SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [TestQueueMsg] (@Message) END SELECT @err = @@ERROR; -- if succeeded, exit the loop now IF (@err = 0) BREAK; SELECT @counter = @counter + 1; IF @counter > 10 BEGIN -- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.' BREAK; END -- We tried on the said conversation, but failed -- remove the record from the association table, then -- let the loop try again DELETE FROM tConversationSPID WHERE [spid] = @@SPID; SELECT @conversationHandle = NULL; END;
We have a production sql server V6.5 sp5a (running on NT 4.0 sp3) that has a tempdb that is fulling up about every two weeks. (The log to tempdb is okay, it is not filling up). Tempdb is defined as 400 MB. I have been reading about "The two most common db errors"; 1) The db log ran out of space and 2) The db ran out of space. We are receiving "The db ran out of space." Once tempdb gets near full is there anyway to clear tempdb without rebooting the nt server? Is there a stored procedure that can be scheduled to free up tempdb?
My books tell me the tempdb is used for creating temporary tables or storing temporary information. For example, a temporary table in a stored procedure or sql may create a temporary work table as a result of a query with a 'group by' or 'order by' clause. If these tables are temporary why don't sql release them or free up the space used by tempdb?
We have a SQL Server 6.5 with SP5a installed and are having problems with the Transaction log not clearing every night when a scheduled backup takes place. Is there anyway to debug this?
The Scheduled task that is run is "DUMP DATABASE DBSALES TO DBSALESBK with INIT DUMP TRANSACTION DBSALES WITH TRUNCATE_ONLY DUMP DATABASE DISTRIBUTION TO DSBK WITH INIT DUMP TRANSACTION DISTRIBUTION WITH TRUNCATE_ONLY"
I have also run on it DBCC Checktable(Syslogs) but it all seems fine. Any help would be appreciated.
I'd like to ask you what means the term "database clearing", if such a concept exists in the Database Theory. Maybe this is not directly related to SQL Server but any help would be very appreciated. So please anybody who have an idea of what this could mean is welcome to reply here
Thank you very much in advance. With best regards, Sorin
I have entered some temporary values in a table located in an SQL database, to test a project to see if it was working. Now I want to remove the values so that later, the program I have created will be capable of using the table for it's true purpose. However, when I try to delete the values located there, I receive an error message. How can I clear the table?
In SQL SERVER 2000, there is a command DBCC OutputBuffer which returns the current output buffer in hexadecimal and ASCII format for the specified system process ID.
Is it so that everytime we execute a stored procedure the contents of the outputbuffer are automatically cleared so that the new contents are inserted into the outputbuffer ?
If it is not cleared then how can we clear this outputbuffer.
1. Is there a way to clear all records in a table from within a store procedure? (Instead of having to go into the table and manually selecting all rows and deleting them that way.)
2. I want to sort a list of records so that the only one left are the ones with just single words in. So I have used
WHERE SearchText LIKE '% %'
to remove the cells with spaces in, but I need to be able to remove the ones with punctuation too. Is there an easy way to do this?
We have been testing a new vendor_purchased application and are now running some month-end processes/jobs now. One of the processes that we kicked off stopped when a 2gb tempdb filled up with only 5mb or so remaini g. In reviewing what happened, I noticed that tempdb is in the process of clearing itself out slowly when we restarted the process. The database has 80% of its disk space remaining and the transaction log has also 75% remaining.
My questions are:
1) Although I don't think so, is there nay way of speeding up the process of tempdb clearing out the data in it?
2) We will need to examine what sql code the vendor has used that caused this to happen. Aside from group by and order by, if there are a lot of 'select into' code, what alternatives do we have?
Any information that can be provided will be fine. THanks in advance.
Having issues with a table clearing out properly which is causing problems in the program.
By design there should only be one row in this table which updates with the most current record. However on this one system the table does not clear/update like it is supposed to, and instead starts adding multiple rows.
I have tried truncating the table but the problem does not resolve itself. Does anyone have any ideas as to why this might be happening?
Hi everyone, I have a question that I believe should be simple to answer yet I cannot find the answer anywhere. I am trying to make it possible for my report to clear the input box whenever the report is run or when anything in a dropdown list is selected. The reason why I want this is because my report has a dropdown list that inputs date ranges for "quick" report info. The other option is to manually type in the begin and end date. If anyone could help me out with this I would be very grateful.
I'm opening a large number of connections to a SQL server 2005 database using a SQL object based on the System.Data.SqlClient library. When I log out of the application, I call the ClearSQLConnectionPool method of the SQL object and this should clear the pool of these connections. This has worked in the past, but lately I have noticed some odd and very annoying behaviour.Very often I log out of the application and few, if any of the connections disappear. Just this morning I opened the application, logged out and all but one of the connections disappeared. Then I opened it again, logged out and none of the connections had gone. If I open the application again all of the past connections are still there plus the new ones.I know it works, its just very inconsistent. The problem may be with IIS, SQL or with library; I just can't tell which.Any ideas?Many thanks
Is there any way to clear out the exception from a previous Try/Catch block if I am nesting another Try/Catch block within it. I am executing a SQL Command and based on the Error number coming back decide whether or not to execute various other Sql commands. Now the Outer exception seems to be taking precedence over the Inner try block, and even though the code is stepped through for the inner try block it is never executed due to the Parent Exception. Is there any way to clear the exception received from the outer Try block? Here is a snippet of code:
Hi,I want to clear all data from 200 tables in a database from a sql query. Some of the tables have got relationship as well. I want to clear data from there as well.What could be the best approach? Does anyone have an idea or script from where I could do the needful?Do let me know.Thanks
May I know how to clear the cache area in the analyser as I need to know the time taken for execution of an sql statement(query) before and after creation of indexes.
I am searching for a resolution. I did a few searches on this site and could not gain the exact answer. I apologize is this thread is a duplicate.
I have written a report containing Multi-Values for Reporting Services. When a user selects the values for the report parameters, the checked values continue to be selected after the report is completed.
Is there a way to clear these parameters?
Some of our users run the same report more than once during a single web session.
Hi, We have an application which fetches data from a table which has approximately 1 million records.
1. Nearly 25 users will be using this application concurrently. 2. frequent updations will be done to the records in the geographyrolecurriculum table. 3. This table has 1 clustered index and 4 nonclustered index bounded to it.
Problem Statement: 1. Application runs smoothly for 15 - 20 days and after that all the screens throws timeout errors. When i clear the sys.syscacheobjects its working fine again and screens get loaded quickly. Please tell me how clearing the syscacheobjects makes the execution fast? and is this the correct way to solve the timeout issue or is there any other alternative?
2. Will the stored procs timeout if the tempdb is full ?
I am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:
Msg 2714, Level 16, State 6, Line 6
There is already an object named '#mytemp2' in the database.
I am developing automated .Net Unit Tests, and as a prerequisite of each test, I would like to clear the service broker queues of any messages. Executing the
RECEIVE * FROM statement appears to only return a message at a time, and not all as I expected. Any ideas on how to make this happen, besides not having to delete the queues and then having to rebuild them?
We see the following message in our error log. WARNING: Clearing procedure cache to free contiguous memory. It is accomonpanied by fairly intensive CPU activity. We get this roughly once per working day.
Anyone have any idea why, and what we can do to stop this?
I am using SSRS 2008 and the reports we have use parameters of type Date/Time. The reports work well when the parameter values are entered correctly.
When entering an invalid date format for one of the Date/Time parameters the following error is displayed "The value provided for the report '<parameter name>' is not valid for its type. (rsReportParameterTypeMismatch). This seems to be working correctly as well. However, when the correct date format is then entered for the report parameter for which the report threw an error, the error persists and the report doesn't run again. Setting the parameter to "NULL" doesn't work either.
The only way to get the report to run again is to refresh the entire report. Of course, if at that point one has entered a bunch of other parameter values, those values all disappear.
I have a system that will post a message to a queue, but does not need to wait for a response - just needs to make sure the message arrived properly in the queue, not that is was processed at the receiving end. A second service will poll the queue to retrieve outstanding messages and will then move the message to an outside system. The movement of the message to the outside system will be wrapped in a transaction and if the process is successful, then the transaction will be commited otherwise it will be rolled back.
1) is it appropriate for the service that posts the message to send an END CONVERSATION ? This way the sending service will not be waiting for a response.
2) in the data movement phase, is it appropriate to issue and END CONVERSATION when commiting and not issue when ROLLBACK occurs. Or should ROLLBACK occur with a following END CONVERSATION with error message?
I am attempting to learn Service Broker from Bob Beauchemin's book "A Developer's Guide to SQL Server" - Chapter 11. I'm finding it to be very good but I'm confused over the concept of closing a conversation. Could someone answer the following questions for me?
When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not) Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply? Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?
