3

Time for experts (waiting for @RemusRusanu and mates^^)

Context

I built a service broker thing, heavily based on SQLTeam's blog example (http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker), to simulate ETL behavior bewteen two databases on the same SQL Server instance. After hours experiencing tricks with brokers, conversations, disabled queues, poisoning messages, activation issues and so on, I finally made it to work.

Questions

To make it a bit more production-proof, I only had to deal with following topics:

  1. CLOSED-status conversations are not being cleared from sys.conversation_endpoints while message queues are emptied: SQLTeam's example does not provide any END CONVERSATION in neither of initiator or receiver side, hence adding comment like

No need to close the conversation because auditing never ends

in activated procedure. Internet is full of testimonials NOT to adopt fire-and-forget behavior. I added some END CONVERSATION at the end of my activated procedure, with the same result. Tons of used conversations. I know about the benefit of reusing dialogs (thanks to @RemusRusanu) but I want the system to be as stable as possible, i.e avoid running out of available endpoint sockets.

=> What am I supposed to REALLY do? end conversations/not? Has LIFETIME to do with such situation?

  1. Trapped errors are not persisted into Errors table, though TRY/CATCH is being used, with INSERT statement placed after the rollback.
  ;SEND ON CONVERSATION @dlgId    
  MESSAGE TYPE [//Sync/Message] (@syncedData)
END TRY
BEGIN CATCH
  IF (XACT_STATE()) = -1
      ROLLBACK;

  INSERT INTO SyncErrors(ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, SyncedData)
  SELECT  ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), @syncedData
END CATCH

=> Why does this never work when error raises?

  1. In some examples, communication looks like being handled with exchange of different explicit message types (error and conversation acknowledgement), on top on custom XML message. As far as I understand, this implies two "crossed" endpoints with accurate queue processing activation procedures (vs one for my current example). There are so many opinions regarding this topic, I am a bit confused. Minutes ago, I found this long http://rusanu.com/2007/10/31/error-handling-in-service-broker-procedures article from @RemusRusanu, but didn't have the chance to give it a look until now.

=> Will this provide stability and robustness increase I am looking for?

  1. Last but nos least. I recently faced a 70+GB unexpected fulfillment of system volume (Windows event log, by the way), withing 2 days, aligned with service broker use, that messed up my virtual machine until I found the root cause (thanks to Treesize Professional tool).

=> Is it normal behavior? Any way to avoid such verbosity, cap event log growth or configure rotation cycles?

Thanks a lot for bringing me to the dark side of the Force :)

tivivi
  • 77
  • 7
  • Stuffing four questions in one is not good practice. It looks like the answers aren't going to be related at all. For example, I imagine the answer to #2 might have something to do with an outer transaction still being active and not committed, but we'd need more context to be sure, and asking for clarification (and the resulting edits) would run together with the other questions. Feel free to 1) take it slow, 2) repeat the context if necessary and 3) link to questions if you really feel they're materially related, but don't make it one "here's everything I can think of" question. – Jeroen Mostert Mar 08 '18 at 15:07
  • @JeroenMostert I get the point, even if I feel a bit frustated. Why opening 4 threads that might be related each others, when one could do the trick without splitting? Main reason may be clarity, OK. By the way, as I didn't have answer, I did a bit more research on my side and, from this thread, I would only keep the last question regarding Windows event log that unexpectedly fills up to death. Any idea? Thanks – tivivi Mar 15 '18 at 11:04
  • It would depend on what kind of events are getting logged, but even so you can always limit the event logs by size and by duration (check their properties). You can turn off SQL Server event logging entirely, but that's probably not what you want to do. – Jeroen Mostert Mar 15 '18 at 11:13
  • For your info, I use the Lifetime to make conversation end because they get stuck in "CLOSED" state and stay there. Our app is made to reload everything from scratch anyway if it's disconnected from the database for too much time so I don't need those messages in that case – Danielle Paquette-Harvey Sep 24 '19 at 19:34
  • @tivivi, I have same errors as you have. do you find any solution? – mostafa8026 Jun 04 '20 at 17:28
  • @mostafa8026 nope – tivivi May 04 '22 at 07:39

0 Answers0