Advice on Error Handling and Conversations

i using service broker auditing.  i've adapted few solutions found around web.  namely 1 service broker part

http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

not reusing conversations article states.  have 2 basic questions

1.  haven't been ending conversations because i'm auditing , never stops.  however, everytime send new message begin new dialog.  creating mess not ending conversations.  decided not based advice read, doesn't seem make sense here.
2.  whenever receive error, continually log error in error table , error table grow huge.  because i'm rolling transaction when error occurs, message requeues, , keeps erroring out.  best thing not roll , record message table.  liked idea continue retry, have write someting not log error if previous error same or affect.

here activation procedure:

declare @messagebody xml	  	declare @dialogid uniqueidentifier  	declare @debug	int  	set @debug = 0  	while(1=1)  	begin  		begin transaction	  		begin try		  			-- insert messages audit table 1 message @ time  			;receive top(1)   					@messagebody	= message_body,        					@dialogid		= conversation_handle      			from	dbo.targetauditqueue  						  			-- exit when whole queue has been processed  			if @@rowcount = 0  			begin  				if @@trancount > 0  				begin   					rollback;  				end    				break;  			end   			  			exec audit.writedatahelper @messagebody, @dialogid  			  			  			  			-- no need close conversation because auditing never ends			  			-- can end conversations if want periodicaly scheduled job  			--end conversation @dialogid    			if @@trancount > 0  			begin   				commit;  			end  		end try  		begin catch  			if @@trancount > 0  			begin   				rollback;  			end  			-- insert error auditerrors table  			insert audit.errors (  					errorprocedure, errorline, errornumber, errormessage,   					errorseverity, errorstate, errordialogid, auditeddata)  			select	error_procedure(), error_line(), error_number(), error_message(),   					error_severity(), error_state(), @dialogid, @messagebody			  		end catch;  	end	

1. leaving stale dialogs around not idea. eat resources other object would. conversations meant long-lived, it's ok not end them every 5 minutes, if case, make sure you're reusing 1 of opened ones when need send message. anyway, interesting see advice told not end conversations.

2. growing table effect theoretical expectation or see in practice? reason ask because seems unlikely happen, given fact after 5 consecutive rollbacks queue disabled , no more messages received. i'm not sure why nice continues retrying if fails. if operation fails happens inside audit.writedatahelper proc, perhaps consider handling there , don't rollback outer transaction received message.

in addition bol topic linked above, can find more information on handling poison messages on service broker team blog: http://blogs.msdn.com/sql_service_broker/archive/2008/06/30/poison-message-handling.aspx .

SQL Server  >  SQL Service Broker