1

I'm using Event Notifications (sending messages to another server), and to make it easy to set up I built it in MSDB (existing database, already has service broker, etc). However, we wound up with... issues... and now I'm trying to clean up 5 conversations, each of which has millions of messages in sys.transmission queue. MSDB is 40gb.

Trying to END even 1 conversation (the smallest, with 3 million messages) made the log grow past 15gb before I killed it.

It appears that I need to ALTER DATABASE SET NEW_BROKER in order to reset everything, which is fine. But since it's MSDB, I would expect DatabaseMail to be affected.

What do I need to do with DatabaseMail.exe once I perform the ALTER? Can I just kill the executable and it'll restart? Do I need to do anything else?

Thanks in advance.

mbourgon
  • 1,286
  • 2
  • 17
  • 35
  • Good question. I would expect dbmail to pick back up (though its queued messages would also be purged). Is it an option to drop/re-add the affected queue? I'd expect that to be a metadata operation like a table drop. – Ben Thul Feb 28 '19 at 19:40
  • @ben-thul Good point - I'll give that a shot. My fear was that it would have similar log repercussions as ending the conversations. – mbourgon Mar 01 '19 at 00:04
  • I'd expect it to be a minimally logged operation. That is, it should mark the data pages as being available for reuse in the GAM instead of needing to be transactionally consistent. Compare it to deleting a million rows from a table vs drop or truncate. – Ben Thul Mar 01 '19 at 00:08

0 Answers0