Bad service

It appears there is a problem with Sql Server Service Broker when executing DROP SERVICE. I had it choke the server to eventually fail with the message "There is insufficient system memory to run this query".

The problem is that Service Broker needs to end all open conversations, and it does so in one transaction. If you have many open conversations troubles arise.

I found the following link which describes a fix for the problem. I'm reprinting the fix here in case the link goes bad:

declare crsConversations cursor for
select conversation_handle
from sys.conversation_endpoints e
join sys.services s on s.service_id = e.service_id
where s.name = 'myservicename';
open crsConversations;
declare @dh uniqueidentifier;
declare @batch int;
select @batch = 0;
begin transaction
fetch next from crsConversations into @dh;
while @@fetch_status =0
begin
-- replace this with end conversation @dh with error ... if
-- and error has to be sent to the peer
--
end conversation @dh with cleanup;
-- commit every 1000 conversations ended
select @batch = @batch + 1;
if @batch > 999
begin
commit transaction;
begin transaction;
end
fetch next from crsConversations into @dh;
end
commit transaction;
close crsConversations;
deallocate crsConversations;
go
drop service 'myservicename'
go

Now, if you have this problem the real question is, why are you leaking open conversations?

(contd.)

My old blog can be found here:
http://blogs.codegear.com/andersivner.
I've since then left Borland/CodeGear and now work for Sigma.

The product I worked on at Borland/CodeGear, ECO, is now available from CapableObjects, a company run by my friends and former collegues. They have recently released ECO for Visual Studio. Way cool!