Target Sys.conversation_endpoints Not Purged Of Closed Conversations
Dec 7, 2006
I hope someone can help me with this as we plan on using Service Broker in a high volume production environment. The script that builds everything is available if it's needed to diagnose the problem I'm having.
I'm having an issue where sys.conversation_endpoints on the target side of a conversation is never getting purged of closed conversations even after the 30 minute delay. The view is filled with closed conversations and database size is growing every day. I'm aware I can end conversation with cleanup on these conversations, but I would prefer that Service Broker behaves as expected. I'm also aware of the problems with the fire and forget model, but my model is request/response/end between 2 databases on the same server instance. Here's the typical series of events:
I implemented the pattern suggested in the 'Recycling Conversations' article that Remus Resanu presented. Everything works great except ended conversations on the receiver remain in the sys.conversation_endpoints table forever in the 'CLOSED' state.
Is there some setting I am missing to have those conversations purged from the endpoints table. I am concerned that in the production environment this table will grow very large.
Hi! I'm wondering why is my sys.conversation_endpoints table inserting a new row for each message i send even when i reuse conversations? when i send the first message i get the first row in the sys.conversation_endpoints with a uniqueidentifier for the conversation_handle. this uniqueidentifier is then saved in the table which i query the next time i send a message to reuse the dialog conversation. But even though it looks like the uniqueidentifier is reused i still get a new row for every message i send with a different conversation_handle? this happens in both target and initator db.
I've tried to understand this by i don't.
Also for the moment i don't end conversations. But as i understand it this shouldn't matter.
Also the message successfully arives to the target and sys.transmission_queue is empty in both databases. Neither queues have any error messages in them.
We have been having a problem with service broker for quite a while now and searching on these forums and more generally on the web has not yielded any kind of answer...
Our application utilises service broker within a single database (there is no cross database, cross instance or cross server communication). There are approximately 12 queues which are used to varying degrees. A few of the queues have a throughput rate in the order of up to 100s per minute at peak periods.
In some scenarios we are able to make use of persistent conversations but the majority of messages are sent on their own conversation or in small batches on their own conversation.
Some time ago we found that the database was growing in size more than expected due to a build up of data in the service broker meta data internal tables, exposed via the sys.conversation_endpoints system view.
We identified a problem in the application that was preventing some conversations from being closed and have now fixed this.
However, we are still experiencing a build up in the service broker tables and sys.conversation_endpoints shows this is now due to a very large number of conversations in the "CLOSED" state.
I know that conversations are kept around to prevent replay attacks but thought they were supposed to be cleared after about 30 minutes. This is certainly not happening as we currently have conversations that were opened on the 18th September, a full week ago. We currently have about 350,000 closed conversations and this figure is increasing!
We have tried restarting the SQL Server instance with no effect.
I have been using a script to loop through all closed conversations and get rid of them using the "WITH CLEANUP" clause but I'm loath to create a scheduled task that does this in the background when service broker should be doing the job itself.
Has anybody experience this problem and, even better, have any idea how to solve it?
I know that if a conversation is normally ended the handle will wind up in the sys.Conversation_endpoints table in a Closed State. I realize they are supposed to stay there for 30 minutes to prevent a reply attack, however the number of rows I have in this table continues to grow with the bulk of the states set to 'Closed'. I am trying to use this table to determine if I have any conversation handle leaks. I see some rows in there with a Disconnected Outbound state while some are conversing. What should I be looking for in this table and how can I know I have a problem (ie. leak). I realize that Disconnected Outbound is probably something i need to look into. Are there any other states I should be concerned with?
So I took the time to build a reproduction of the conversation_endpoint problem that was discussed in another thread. I build two databases, with a send and receive queue. This is essentially the way the code works here at my site. I have a script near the bottom that sends messages every 5 minutes for 2 hours. If there is any logic that removes conversation_endpoints 30 min then the Message record table will show them.
Please let me know what I am doing wrong, so I can change my production code to help eliminate the large buildup in the sys.conversation_endpoints.
Thanks!
use master
go
if exists ( select * from sys.databases where name = 'SBSource' )
drop database SBSource
go
if exists ( select * from sys.databases where name = 'SBTarget' )
drop database SBTarget
go
-- Setup environment for test
create database SBSource
GO
ALTER DATABASE SBSource SET ENABLE_BROKER
ALTER DATABASE SBSource SET TRUSTWORTHY ON
GO
create database SBTarget
GO
ALTER DATABASE SBTarget SET ENABLE_BROKER
ALTER DATABASE SBTarget SET TRUSTWORTHY ON
GO
use SBSource
go
CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];
CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );
CREATE QUEUE dbo.[SourceQueue] WITH STATUS = ON , RETENTION = OFF;
Alter QUEUE dbo.[SourceQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessEndDialogMessages, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )
go
use SBTarget
go
CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];
CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );
CREATE QUEUE dbo.[TargetQueue] WITH STATUS = ON , RETENTION = OFF;
Alter QUEUE dbo.[TargetQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessTargetQueue, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )
go
use sbsource
go
-- start sending messages, check count in conv_endpoints along the way
set xact_abort on
set nocount on
declare @EndAt datetime,
@msg xml,
@ch uniqueidentifier;
set @EndAt = DATEADD( hh, 2, getdate() )
set @msg = '<message>dfsafa</message>';
while getdate() < @EndAt
begin
set @ch = null;
begin transaction;
begin dialog conversation @ch
from service [SBSourceTest]
to service 'SBTargetTest'
on contract [Test]
with
encryption=off;
send on conversation @ch message type [msgTest] (@msg);
--- note the abscence of an end conversation, so no fire and forget!
commit;
waitfor delay '00:05:00'
end
GO
-- check on data after complete.
select state, count(*)
from SBSource.sys.conversation_endpoints
group by state;
select state, count(*)
from SBTarget.sys.conversation_endpoints c
inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle
group by state;
select m.*, c.*
from SBTarget.sys.conversation_endpoints c
inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle
Is there any thought going into moving these two tables to a file group that we can control? Putting this in Primary with the rest of my system tables is quite problematic, and hinders my ability to manage space usage on my files. Traditionally, we didn't have to consider a primary file group that could grow to large proportions, but now with these two tables it can. If a large volume of messages gets sent through and the system can't keep up, then these tables and my primary file group will grow sometimes enormously.
I am currently designing an auditing application using Service Broker. Right now, when I send a message from a trigger, I start a conversation, and later on when the message has been processed, the conversation has ended. One thing I am concerned with is that when a lot of updates are occurring on the system, if the amount of conversations being created will eat up system resources. Does it make sense to create them and end them later, or should I try to reuse them? Tim
I have a server that is used to query other servers to ensure that things are functioning correctly. On some of our servers we are running ServiceBroker and some of the monitoring involves querying the SYS.CONVERSATION_ENPOINTS view on these servers.
When I query the SYS.CONVERSATION_ENPOINTS view over a linked server it returns zero rows, though when I run the same query locally it returns data. I initially thought it was a permission issue but it seems I can see data across the linked server to other system views within the sys schema (ie sys.all_objects).
Is there something different/special about SYS.CONVERSATION_ENDPOINTS that prevents me from seeing its data across linked servers or have I simply got permission problems.
Most of the examples I've found and played with demonstrate two way conversation. A sender initiates a call, and gets a message back.
My Requirements doesn't really need two way communication. I have a scenario where triggers on two different tables result in modifications to a third table, and I don't want the triggers to deadlock each other, so an asynchronous queueing mechanism seems like the perfect solution...
But I can't seem to make it work one way.
I can get one message through, and then all subsequent messages hang up in the transmission queue with the very informative "One or more messages could not be delivered to the local service targeted by this dialog."
I'm thinking all the examples work the way they do because you have to notify the transmitter that the message was received by sending a message back... and by not doing this I'm stuck in the first conversation. I was thinking that by doing END CONVERSATION <Msg Handle> in the stored procedure bound to the receiver's queue was doing that.
Do I have to communicate bi-directionally always? I guess this is a safety feature but I trust MSMQ to deliver messages...
Say I have a conversation established and the initiator server needs to reboot. Will the conversation automatically restart when the server comes back up? If not, can I get it to with some setting? If not, what is the best way to handle this?
In my application, I need to be able to guarantee that processing for a re-used conversation is completed prior to starting processing the next (re-used) conversation. My application is based on the concepts from the sample posted on Remus's blog: http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx#comments). Essentially (in this sample), we create a new conversation for each SPID and re-use the conversation, so that messages are sent through the queue (and processed in order) for each SPID. SPID was used in the sample code as an example of some application-specific "thing" that you care about message ordering for. To prevent a conversation from living forever (using up log/resources), they are ended after 1 hour using DialogTimer and a customer message type.
My conundrum is this:
Assume conversation 1 (on SPID 1) is flooded with a large number of messages just before the conversation timer expires. The DialogTimer then expires before the target queue is drained. The sample code (mentioned above) then creates a new conversation for the same SPID (with a DialogTimer of 1 hour). Until the queue for conversation1 is drained, we have 2 conversations being processed for the same SPID. This same problem would occur in any application where you re-use conversations for a period of time (using DialogTimer), and then start a new conversation when the DialogTimer expires.
So although I like having the idea fof being able to re-use conversations, I would need to guarantee that conversation 1 is finished processing before conversation 2 starts processing (for the same SPID, to be consistent with the sample above). If I could get these 2 conversations into the same conversation group on the target queue, the CG locking would solve the problem. But because conversation groups only apply to the initiator queue (when you begin dialog with related conversation), I have no "out of the box" way to control how the conversation groups are associated on the target queue. Remus posted an idea here (bottom of thread): http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=182646&SiteID=1, which was to just send a special message at the beginning of each new conversation (containing the conversation group to use), and then doing a move conversation to conversationgroupid on the target queue. I've tried this solution, and the problem is 1.) if the set convo message fails for some reason, the conversation group is not set and 2.) if the target queue seems to reject most of my move conversation commands with the error "The destination conversation group '<conversation guid>' is invalid." - which I am guessing is due to the fact that this convo group id is being used on the initiator as well.
We have a system that has 35 million conversations piled up. We didn't know to explicitly end the conversation once the processing has completed. Oops. Now, our production box has 35 mm sitting in the table, and we have run into the problem where the amount in sys.conversation_endpoints has exceeded memory and they are being dumped into tempdb, which is killing our disk space, thus bringing the box down. We have fixed the code to end the conversations, but we now have to end the conversations in a hurry. If we select one by one out of the table and end the conversation via END CONVERSATION, it is slow. Very slow. It will finish in a few months. :(
Does anyone know how to get rid of these conversations in a hurry? All of the messages have been applied to our system, so killing the conversations will (should) have no affect on the processed data. Something like a TRUNCATE statement?
I have implemented Remus Resanu's implementation from the Recycling Conversations article and I am experiencing locking issue when I try to insert new conversation handles to the SessionConversations table. I have copied the code in the article exactly including the activation procedure. Any ideas why I may be locking. I am thinking it is related to the HOLDLOCK hint on the table.
The sepcific line where I see locking is directly from the article:
INSERT INTO [SessionConversations] (SPID, FromService, ToService, OnContract, Handle) VALUES (...etc)
I have an sp, which has 2 select statements, so iam using a sqldatareader and binding the data to a dropdown. the first binding is fine, but when i say dataReader.NextResult(), It is null.It says the reader is closed. Can any one tell a work around for this.
without con.close(),it can return Resultset ,but when includeing con.close(),an Exception tell me:Object has been closed, in other programms i've close connection,but it never throws this Exception,why? thanks
I have the Function, that fires from onLoad even of one of the asp:Label controls on my main page. Here is it's code: SqlConnection conn = new SqlConnection(); conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["UkraineConnectionString"].ToString(); SqlCommand comm = new SqlCommand("SELECT [Greeting] FROM [Misc]", conn); try { conn.Open(); } catch { Response.Write("Error opening connection in Page_Load of default.aspx to retrieve the greetings"); } string MyGreet = (string)comm.ExecuteScalar(); Greetings.Text = MyGreet; try { conn.Close(); } catch { Response.Write("Error closing connection in Page_Load of default.aspx after retrieving the greetings"); } } When it gests to conn.Open() in the debugging mode I see that the ServerVersion = 'conn.ServerVersion' threw an exception of type 'System.InvalidOperationException'. The most interesting thing is that it used to work before. Here is the connection string it retrieves fine. "Data Source=MDM1;AttachDbFilename=|DataDirectory|Ukraine.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" As I said it used to work, but one day I tried to access the web site and it said this error that I get, that the connection is closed. Then I was using the SQL Server Express. When in the Visual studio if I would run this same site in debug, everything was working fine. I decided to uninstall the SQL Express and installed the SQL Server. If I open the SQL Server Managment Studio in the Server name field I see MDM1(this is the name of the PC, but probably it is the same name for the Server. Well, in the MAnagment Studio it conects fine to the MDM1 so it is no probably the naming problem. Ithink it has something to do with permisssions. If someon can - please help. Thanks.
Hello, I have built a system, that uses a .dll file for all SQL operations. So a program looks something like this: using myDLL; .... SQLDBCON mSQL = new SQLDBCON(); and here comes the rest of the program. My question is, the dll file has all stored procedures and when you declare mSQL as shown above, then it opens a thread to the database for that user. Is that thread properly closed when the site has finished loading? in my .aspx page i dont have a function like mSQL.CloseDB(); and if i try to add something like this to the dll file ~SQLDBCON { m_local_con.Close();m_local_con.dispose(); } i get a error message says something that this is not allowed. Just want to know if my thread in the dll file is properly closed?
I'm getting object closed when returning a recordset from a stored procedure. I've tested the select statement in Query Analyzer and a value is getting returned. So, I'm not sure if my code for my stored procedure is incorrect?
Here is the code for the procedure:
CREATE Procedure dbo.GetRepEmailByZip
@sessionid varchar(50), @zip varchar(5)
AS
Begin Transaction
INSERT INTO dbo.SupportRequests(firstname,lastname,schoolname, address,city,state,zip,phone,email,currentcustomer ,implementationtype,producttype,comment) (SELECT FirstName,LastName,SchoolName,Address,City,State,Z ip,Phone,Email,CurrentCustomer,ImplementationType, ProductType,Comment FROM dbo.Temp_ContactInfo WHERE sessionid = @sessionid)
--If Transacation fails, stop execution of procedure, return error code and Rollback Transaction
IF @@ERROR<>0 OR @@RowCount = 0 BEGIN ROLLBACK TRANSACTION --return value RETURN 1 END
--If Transaction succeeds, commit transaction, continue and process the select statement COMMIT TRANSACTION
SELECT r.email FROM PostalCodes p INNER JOIN TerritoryList z ON p.ZipID = z.ZipID INNER JOIN RepList r ON r.RepID = z.RepID WHERE p.ZipCode = @zip GO
This is the code I'm calling to execute the procedure and return the recordset:
set GetRepEmail = Server.CreateObject("ADODB.Command") With GetRepEmail .ActiveConnection = MM_DBConn_STRING .CommandText = "dbo.GetRepEmailByZip" .CommandType = 4 .CommandTimeout = 0 .Prepared = true .Parameters.Append .CreateParameter("@RETURN_VALUE", 3, 4) .Parameters.Append .CreateParameter("@sessionid", 200, 1,50,usrid) .Parameters.Append .CreateParameter("@zip", 200, 1,5,zip) set RepEmail = .Execute() End With
Dim x, y x = RepEmail.RecordCount y = RepEmail.State Response.Write(x) Response.Write("<br>") Response.Write(y) 'Response.Write(RepEmail("email")) Response.End()
I'm getting "Operation is not allowed when the object is closed", which is occuring on the following line:
x = RepEmail.RecordCount
I'm trying to determine if this is problem within my procedure or in the application code.
I have a page that I have 3 connections. I've made sure that each of these are closed when they are not being used and opened just right before being used. I keep getting the error "There is already an open DataReader associated with this Command which must be closed first." This error might show up as being produced by a dataadapter or sqldatareader...I have many. I've even tried to make separate connections as some have mentioned for each...leaving me with 15+ connections. I have added "MultipleActiveResultSets=True" to the connection strings as some have mentioned. I just don't know where to go from here... Is it possible that the problem lies in multiple instances of this page being opened? Also, the data refreshes every 15 seconds. I really need this to work, but I have no clue on how to fix this problem. The error is easy to reproduce by opening up multiple instances, but some of the times is doesn't give an error at all?!
I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.
So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.
Has anyone got any ideas on how to get round this?
I have gathered from reading online that I need to create a 2nd connection to SQL Server if I want to insert records into the database while in a "while (reader.Read())" loop.
I am trying to make my code as generic as possible, and I don't want to have to re-input the connection string at this point in my code. Is there a way to generate a new connection based on the existing open one? Or should I just create 2 connections up front and carry them around with me like I do for the 1 connection now?
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table CaseID, CaseNumber, CaseName
CaseChild Table CaseID, FK to CaseInfo ChildPartyID, FK to PartyID in Party table ProgramClosureDate
Party Table ID, PartyID, Firstname, LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate FROM CaseInfo ci JOIN CaseChild cc ON ci.CaseID = cc.CaseID JOIN Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
I have written a stored proceedure for MSSQL that needs to run for hours ata time. I need to execute it from C++ code. The current code does:nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)followed shortly after by aFree_Stmt_Handle(hstmt) //roughlyThe stored proc currently dies with the statement handle, not fullypopulating the table I need it to.I need to either know when the proc finishes so I can close the handle afterthat, or allow the proc to run independently on the server no matter whatthe program is doing (is exited, etc), either of these is fine.Please Help! Thanks in advance!Joseph
Can anybody tell me why a) when running a stored proc from an asp page toreturn a recordset the command succeeds if the sp queries an existing tabledirectly, but b) if the stored proc populates results into a differenttable, temporary table, global temp table, or table variable, then queriesone of these, the asp page reports that the recordset object is closed. Ifusing a table, I have set grant, select, update, delete permissions for theasp page user account, so it doesn't appear to be a permissioning issue. Ifrun in Query Analyser the sp runs fine of course.Abridged asp code is as follows:StoredProc = Request.querystring("SP")oConn.ConnectionString = "Provider=SQLOLEDB etc"oConn.Openset oCmd = Server.CreateObject("ADODB.Command")oCmd.ActiveConnection = oConnoCmd.CommandText = StoredProcoCmd.CommandType = adCmdStoredProcoCmd.Parameters.Refresh'code here that populates the parameters of the oCmd object correctlySet oRs = Server.CreateObject("ADODB.Recordset")With oRS.CursorLocation = adUseClient.CursorType = adOpenStatic.LockType = adLockBatchOptimistic'execute the SP returning the result into a recordset.Open oCmdEnd With' Save data into IIS response objectResponse.ContentType = "text/xml"oRs.Save Response, adPersistXML'the line above fails with stored procs from example B below, reporting "notallowed when object is closed", but works with example ASP Example A - this one works fineCreate Proc spTestA ASSELECT ID FROM FileListGOSP Example B - this one doesn't work from ASP but runs fine in QACreate Proc spTestB ASDECLARE @Results Table (ID TinyInt)INSERT INTO @Results SELECT ID FROM FileListSELECT ID FROM @ResultsGOI can see the SP executing using profiler when the asp page is called forboth sp's above, so it doesn't appear to be a problem with the execution.It's something to do with returning the result set from the table variable.Thanks,Robin Hammond
The application server gets below error while the job is being run intermittently:
An error occurred while performing connection management
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171) at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:319) at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1839)
