MS SQL server msdb.sysxmitqueue table bloat

[AlexanderSavelyev]

The msdb.sysxmitqueue table ends up with millions of records. Our dba has put together some questions to try to track down this issue.

1. What is the Service Broker used for in the application? What is the flow and purpose of the messages
2. Is it required in our implementation?
3. If not, Can it be disabled or removed?
4. The msdb.sysxmitqueue system table in msdb database had over 30m queue entries and bloated the DB to 35GB. Has this issue been reported before?
5. If so, what was the root cause and how is it normally fixed?
6. If not, what could potentially cause this issue - we have no message queue entries currently to help in debugging as the queue was so large it was unresponsive and needed to be reset.
7. Does the issue happen only on some versions of SQL Server - 2017 Enterprise, 2017 Express etc?

[AlexanderSavelyev]

the only place there the service broker is used https://github.com/epam/Indigo/blob/1196b66f9f889359eab50fd705b6b8ab16c24455/bingo/sqlserver/sql/bingo_create.sql#L115 where it subscribes an event from databases, e.g. remove index if a table was removed So a possible step to reproduce is to loop create table create index drop table

[AlexanderSavelyev]

possible fix to to use clean for the queue

declare @c uniqueidentifier
while(1=1)
begin
    select top 1 @c = conversation_handle from [$(bingo)].notify_queue
    if (@@ROWCOUNT = 0)
    break
    end conversation @c with cleanup
end

[thomaskoppcsharp]

-- What condition/action causes a sys.conversation_endpoints object to go from status_desc STARTEDOUTBOUND to CONVERSING? -- Can the event notification be created on the DATABASE level instead of the SERVER level, thus: create event notification $(bingo)$(database)_logout_notify on database

[AlexanderSavelyev]

We managed to reproduce the issue. Unfortunately, there are no simple solutions, e.g. it is not possible to change the notification from server to database level since the necessary events can only be taken from the database level. Overall the problem is that backup overwrites the database notifications Right now we are working on updating the installation script to be able to fix the following scenarios:

• install backup without bingo on a database with bingo
• install backup with bingo on a database without bingo
• install backup with bingo on a database with bingo (with the same id)

The solution will require to execute the installation script after each backup restore. @thomaskoppcsharp will it work for you?

[f1nzer]

Additionally, as a possible solution, to stop sysxmitqueue flooding, you can manually delete previously created event notification (from bingo installation) using the next SQL command:

drop event notification bingo_DBNAME_logout_notify on server

Created event notifications could be retrieved using:

select * from msdb.sys.server_event_notifications

[AlexanderSavelyev]

some of the issues were resolved in #378