Your activation stored procedure is the problem. Before you configured the queue for activation did you test the procedure by executing it? I am going to bet not in this case. If you run the procedure as written it will provide the following error:

Msg 4121, Level 16, State 1, Procedure USP_ProcessAuditDatabaseManagementEvents, Line 47

Cannot find either column "message_body" or the user-defined function or aggregate "message_body.value", or the name is ambiguous.

Msg 266, Level 16, State 2, Procedure USP_ProcessAuditDatabaseManagementEvents, Line 47

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

The problem is the code below:

select

@ApplicationName=message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')

,@DatabaseName=message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')

,@DBUserName=message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')

,@EventSubClass=message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )

,@HostName=message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')

,@LoginName=message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')

,@NTDomainName=message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')

,@NTUserName=message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')

,@ObjectName=message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')

,@OwnerName=message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')

,@ServerName=message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')

,@SessionLoginName=message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')

,@StartTime=message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')

,@Success=message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )

,@TextData=message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

It should be the code below:

select

@ApplicationName=@message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')

,@DatabaseName=@message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')

,@DBUserName=@message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')

,@EventSubClass=@message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )

,@HostName=@message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')

,@LoginName=@message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')

,@NTDomainName=@message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')

,@NTUserName=@message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')

,@ObjectName=@message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')

,@OwnerName=@message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')

,@ServerName=@message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')

,@SessionLoginName=@message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')

,@StartTime=@message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')

,@Success=@message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )

,@TextData=@message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

You referenced a column name and not the variable that you set the message_body to so it errors out and you get the rollback, so 5 times and your queue disables. Anytime you are building an activation procedure, you need to run it to test that it works without error before configuring the queue for activation using the procedure or you can have this problem.