Closing ADO objects

I know it's "best practice" to dispose ado.net objects, but does it make a big difference if just the connection is closed?

In other words, is the code below good enough or should the DataAdapter & Command be explicitly closed?

using (SqlConneciton cn = new SqlConnection(connstr))

{

SqlDataAdapter da = new SqlDataAdapter(sql,cn);

DataSet ds = new DataSet();

da.Fill(ds);

SqlCommand cmd = new SqlCommand(someOtherSql,cn);

cmd.ExecuteNonQuery();

}

Most will be handled by the garbage collector and its all around no big deal. As far as I know, th eone you need to worrie about a lot, is the SqlDataReader: not closing these make for crazy performance overhead.|||

Hi John,

Actually, you don't need to call dispose. The Dispose method is used to release unmanaged resources. Since Close has already been called, the connection will be put back to pool automatically.

IMO, just call Close. That's enough. If you're using "using" statement, it's better. It will call dispose automatically.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Closed transaction keeps running...awaiting command

I am debugging an app which blocks many processes in a SQL7 server DB.
The app log writes every transaction "open" and "close".
The weird thing is : when the app logfile says the transaction is
dropped (object closed) the db keeps showing the process "running", in
a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
command status.

We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
IIS side, an equal box for MTS (same patches and updates) and a SQL
Server 7 (on NT4, same fixes ans SPs) database on another box.

Is this normal? Those sleeping processes are blocking other apps and
everything gets slow and messy...the only solution is to kill those
blocking processes.

Thanks![posted and mailed, please reply in news]

Rittercorp (ritter_cl@.yahoo.com) writes:
> I am debugging an app which blocks many processes in a SQL7 server DB.
> The app log writes every transaction "open" and "close".
> The weird thing is : when the app logfile says the transaction is
> dropped (object closed) the db keeps showing the process "running", in
> a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
> command status.
> We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
> IIS side, an equal box for MTS (same patches and updates) and a SQL
> Server 7 (on NT4, same fixes ans SPs) database on another box.
> Is this normal? Those sleeping processes are blocking other apps and
> everything gets slow and messy...the only solution is to kill those
> blocking processes.

No, this is not normal. Apparently your application has a problem with
transaction scope. The fact that your application log looks good proves
little. The application log may know about transaction it starts, but
what about transactions that starts in SQL code, for instance in
stored procedures?

There are a couple of gotchas. For instance, if you call a stored procedure
and that stored procedure starts a transaction, but the application
cancels the procedure before the execution completed, for instance
because of the dreaded "Timeout expired", the transaction started by
the SP is *not* rolled back. The same is true, if the procedure
starts a transaction, and then is aborted because of a reference to
a non-existing table.

On http://www.sommarskog.se/sqlutil/aba_lockinfo.html I have a
utility that gives yuu a lot of information about locks in the server,
including a list of which are the locked objects. This might be helpful
for you to understand where you are leaking transactions. (Although it
might be messy to find out. Because once a process has missed a
COMMIT or ROLLBACK, it will accumulate locks from all over the place.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Closed not Answered - New Call with updates

ok here goes I uninstalled SQL Server Express and decided to Install the Standard Edition - Wow that was huge to do - lots of little things left

I have now installed SQL Server 2005 Standard.

But I cannot configure it properly.

(How I know this) I can open Business Intelligence Development Studio - can after some initial fiddling around

design a report/data/layout/preview - but cannot render a report to either

my local machine - nz3/reports

or to an external vmw server

My Report configuration is not complete and initialisation is not complete.

I am aware there are lots of instructions on books online , I have been reading them all as well.

But other than going round and round in circles I don't know how or in what order to tackle them.

I have a document with screen shots that may be much more helpful to explain

Is there anyone willing to jump in and see if they can guide me through this.

ok just an update here - but I still need some help! if someone would like to jump in

I have configured everything in Reporting Services configuration but initialisation is still not going.

All my services are running - my home page = localhost is showing this:

Your Web service is now running. You do not currently have a default Web page established for your users.

But on configuration my virtual report server is ticked?

What else could I check?

thanks

|||Re: SQL Server 2005 setup with Reporting Services Configuration

Closed conversation endpoints not being purged

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?

Daniel

Hi Daniel!

Sounds very interesting. Do you close the conversation on both ends of the dialog with an END CONVERSATION statement?
Normally the conversations should go away from sys.conversation_endpoints within 30 minutes as you have wrote...

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||

Hi Daniel!

Sounds very interesting. Do you close the conversation on both ends of the dialog with an END CONVERSATION statement?
Normally the conversations should go away from sys.conversation_endpoints within 30 minutes as you have wrote...

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||Yes, we should be closing at both ends. However, I've not yet confirmed this 100%. I was working on the assumption that if the endpoint was in the "closed" state, we must have closed it at both ends (which is what the documentation suggests). Given that SB doesn't seem to be working as expected, perhaps the endpoint state is also suspect? I'll profile the system and see if I can trace the end conversations.|||

The endpoint states meaning is described here: http://msdn2.microsoft.com/en-us/library/ms176082.aspx. CLOSED indeed means both endpoints had issued END CONVERSATION.

What is you message exchange pattern? Who is issuing END CONVERSATION first, the initiator or the target? Is the target ever sending any replies back to the initiator?

Thanks,
~ Remus

|||

Thanks for considering this Remus. I'm a little busy and haven't yet had a chance to look into this to keep comprehensive answers; maybe I can spend more time on this later today.

We have a mixture of message exchange patterns. Most are fire and forget (no response from target to initiator) and in these situations the initiator starts a conversation sends a message and ends its side of the conversation with no delay; the target then picks up the message at some point in the future, processes it and then ends the other side of the conversation. We also have a few request/response conversations; generally we have seperate processes responsible for sending requests and processing responses; there are no pseudo-synchronous conversations.

I may have some more detailed information once I've analysed a trace of service broker activity in this database.

|||

Here are some examples of the service broker activity:

546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Initiator DISCONNECTED_OUTBOUND
546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Target DISCONNECTED_INBOUND
546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Initiator CLOSED

221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.083 Initiator STARTED_OUTBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Target STARTED_INBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.160 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.160 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.193 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.193 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Initiator DISCONNECTED_OUTBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Target DISCONNECTED_INBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Initiator CLOSED
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:58:38.330 Target CLOSED

C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.977 Initiator STARTED_OUTBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Initiator CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Target STARTED_INBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Target CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:13.180 Initiator CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:13.180 Target CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.837 Initiator DISCONNECTED_OUTBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.837 Target DISCONNECTED_INBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.853 Initiator CLOSED


I'm not sure this tells us much but I am suprised about the location of the DISCONNECTED_OUTBOUND messages as I thought our code typcically ended the conversation immediately after sending the message(s) without waiting for a response. Is this because I traced the service broker activity events rather than the actuall T-SQL statements issuing the service broker commands ("END CONVERSATION" etc)?

Daniel

|||

The pattern that leaves target endpoints stranded is the one where the target never sends any message (including EndDialog message). So try not to do fire and forget patterns, they have other faults too. If you don't want/can't change this, then you should set up a task to periodically clean them up (using ENd CONVERSATION ... WITH CLEANUP). The criteria is conversation endpoints that are CLOSED and last activity is more than 30 minutes in the past.

HTH,
~ Remus

|||

Daniel Renshaw wrote:

I'm not sure this tells us much but I am suprised about the location of the DISCONNECTED_OUTBOUND messages as I thought our code typcically ended the conversation immediately after sending the message(s) without waiting for a response. Is this because I traced the service broker activity events rather than the actuall T-SQL statements issuing the service broker commands ("END CONVERSATION" etc)?

I hop I'll g et this right, I'm doing it from memory:

The transition CONVERSING->DISCONNECTED_OUTBOUND is when the initiator issues END CONVERSATION.
The transition CONVERSING->DISCONNECTED_INBOUND is when the EndDialog message sent by initiator arrives at the target
The transition DISCONNECTED_OUTBOUND->CLOSED is when the acknowledgement for the initiators EndDialog message comes back to it.
The transition DISCONNECTED_INBOUND->CLOSED is when the target issues END CONVERSATION in response to the EndDialog message received from initiator.

The pattern shown here is one of the pattern that leaves the target endpoint stranded. Because it never sent any message to the initiator, it never received an acknowledgement. An ack from the initiator sent back to the target serves as positive proof that the initiator will not retry to send message 0. W/o this proof the target cannot delete itself, it has to stay around indefinetly to prevent a replay of message 0, resulting in a stranded target endpoint. That's another reason why the fire-and-forget pattern is ... unrecommended.

BTW, you should also add the EventSubclass column to the profiler view, it details what kind of conversation event we're seeing.

HTH,
~ Remus

|||

Thanks Remus, I'll implement the background cleanup for now. We may be revisting our core principles at some point in the future based on the lessons learnt over the past few months. We've found that tables may have been a more appropriate solution than the many queues we've ended up with.

For example, a message arrives and we put a record in a table as an audit/status information. This continues through the system, each message/step typically generates further records being inserted/updated in regular tables so that we can keep track of what's going on. This has caused tremendous bloating/duplication of data and massive transaction logs so we're now asking ourselves: why not put the message in that same table and forget the queues.

When we come to reconsider all that, we'll ensure fire-and-forget is removed from any service broker elements that remain.

Daniel

|||This saved me from pulling my hair out... I've added it to my blog.

http://www.nootz.net/index.php/notification-services-conversation-cleanup.html

Thanks,
Garick

Closed conversation endpoints not being purged

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?

Daniel

Hi Daniel!

Sounds very interesting. Do you close the conversation on both ends of the dialog with an END CONVERSATION statement?
Normally the conversations should go away from sys.conversation_endpoints within 30 minutes as you have wrote...

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||

Hi Daniel!

Sounds very interesting. Do you close the conversation on both ends of the dialog with an END CONVERSATION statement?
Normally the conversations should go away from sys.conversation_endpoints within 30 minutes as you have wrote...

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||Yes, we should be closing at both ends. However, I've not yet confirmed this 100%. I was working on the assumption that if the endpoint was in the "closed" state, we must have closed it at both ends (which is what the documentation suggests). Given that SB doesn't seem to be working as expected, perhaps the endpoint state is also suspect? I'll profile the system and see if I can trace the end conversations.|||

The endpoint states meaning is described here: http://msdn2.microsoft.com/en-us/library/ms176082.aspx. CLOSED indeed means both endpoints had issued END CONVERSATION.

What is you message exchange pattern? Who is issuing END CONVERSATION first, the initiator or the target? Is the target ever sending any replies back to the initiator?

Thanks,
~ Remus

|||

Thanks for considering this Remus. I'm a little busy and haven't yet had a chance to look into this to keep comprehensive answers; maybe I can spend more time on this later today.

We have a mixture of message exchange patterns. Most are fire and forget (no response from target to initiator) and in these situations the initiator starts a conversation sends a message and ends its side of the conversation with no delay; the target then picks up the message at some point in the future, processes it and then ends the other side of the conversation. We also have a few request/response conversations; generally we have seperate processes responsible for sending requests and processing responses; there are no pseudo-synchronous conversations.

I may have some more detailed information once I've analysed a trace of service broker activity in this database.

|||

Here are some examples of the service broker activity:

546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Initiator DISCONNECTED_OUTBOUND
546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Target DISCONNECTED_INBOUND
546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Initiator CLOSED

221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.083 Initiator STARTED_OUTBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Target STARTED_INBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.160 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.160 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.193 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.193 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Initiator DISCONNECTED_OUTBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Target DISCONNECTED_INBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Initiator CLOSED
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:58:38.330 Target CLOSED

C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.977 Initiator STARTED_OUTBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Initiator CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Target STARTED_INBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Target CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:13.180 Initiator CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:13.180 Target CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.837 Initiator DISCONNECTED_OUTBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.837 Target DISCONNECTED_INBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.853 Initiator CLOSED


I'm not sure this tells us much but I am suprised about the location of the DISCONNECTED_OUTBOUND messages as I thought our code typcically ended the conversation immediately after sending the message(s) without waiting for a response. Is this because I traced the service broker activity events rather than the actuall T-SQL statements issuing the service broker commands ("END CONVERSATION" etc)?

Daniel

|||

The pattern that leaves target endpoints stranded is the one where the target never sends any message (including EndDialog message). So try not to do fire and forget patterns, they have other faults too. If you don't want/can't change this, then you should set up a task to periodically clean them up (using ENd CONVERSATION ... WITH CLEANUP). The criteria is conversation endpoints that are CLOSED and last activity is more than 30 minutes in the past.

HTH,
~ Remus

|||

Daniel Renshaw wrote:

I'm not sure this tells us much but I am suprised about the location of the DISCONNECTED_OUTBOUND messages as I thought our code typcically ended the conversation immediately after sending the message(s) without waiting for a response. Is this because I traced the service broker activity events rather than the actuall T-SQL statements issuing the service broker commands ("END CONVERSATION" etc)?

I hop I'll g et this right, I'm doing it from memory:

The transition CONVERSING->DISCONNECTED_OUTBOUND is when the initiator issues END CONVERSATION.
The transition CONVERSING->DISCONNECTED_INBOUND is when the EndDialog message sent by initiator arrives at the target
The transition DISCONNECTED_OUTBOUND->CLOSED is when the acknowledgement for the initiators EndDialog message comes back to it.
The transition DISCONNECTED_INBOUND->CLOSED is when the target issues END CONVERSATION in response to the EndDialog message received from initiator.

The pattern shown here is one of the pattern that leaves the target endpoint stranded. Because it never sent any message to the initiator, it never received an acknowledgement. An ack from the initiator sent back to the target serves as positive proof that the initiator will not retry to send message 0. W/o this proof the target cannot delete itself, it has to stay around indefinetly to prevent a replay of message 0, resulting in a stranded target endpoint. That's another reason why the fire-and-forget pattern is ... unrecommended.

BTW, you should also add the EventSubclass column to the profiler view, it details what kind of conversation event we're seeing.

HTH,
~ Remus

|||

Thanks Remus, I'll implement the background cleanup for now. We may be revisting our core principles at some point in the future based on the lessons learnt over the past few months. We've found that tables may have been a more appropriate solution than the many queues we've ended up with.

For example, a message arrives and we put a record in a table as an audit/status information. This continues through the system, each message/step typically generates further records being inserted/updated in regular tables so that we can keep track of what's going on. This has caused tremendous bloating/duplication of data and massive transaction logs so we're now asking ourselves: why not put the message in that same table and forget the queues.

When we come to reconsider all that, we'll ensure fire-and-forget is removed from any service broker elements that remain.

Daniel

|||This saved me from pulling my hair out... I've added it to my blog.

http://www.nootz.net/index.php/notification-services-conversation-cleanup.html

Thanks,
Garick

Closed

thx but this code not needed anymore

sry

Please be a little more clear. I can't help, if i don't understand.

Close all open connections

Before I can drop an mdf file form the server, all connections needs to be closed. how can I force to close this connection. The solution explained on this blog don't seems to work in my case http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

I'm using SQL express, with visual studio pro 2005.

Thx for you quick responses

best regards

Luc N

please verify the code I've used

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim com As New SqlCommand("sp_detach_db", con)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

'com.CommandType = Data.CommandType.StoredProcedure

'com.Parameters.Add(New SqlParameter("@.dbname", d.Name))

'com.ExecuteNonQuery()

'MsgBox(d.UserName.ToString())

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

'com.CommandText = "DROP DATABASE " & d.Name.ToString

'com.CommandType = CommandType.Text

'com.ExecuteNonQuery()

'End If

End If

Next

com.Connection.Close()

Why do you think that the information form the blog is not working for you ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

I've still got the message 'cannot drop the database because.......................'

I've modified my code , which seems to be working,

please reply your comments on this code

thx Luc

Try

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

Exit For

End If

Next

If InStr(d.Name, "exp", CompareMethod.Text) <> 0 Then

svr.KillAllProcesses(d.Name)

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

con.Close()

End If

Catch ex As Exception

' MsgBox(ex.Message)

End Try

|||As I pointed out in the comment, they fixed the behaviour in the Service Pack, so KillDatabase should work for you.