sql server - Remove an orphaned sys.service_queue -
i have orphaned entry in sys.service_queues
table.
when do
select * sys.service_queues name '%sqlquerynotificationservice-%' select * sys.objects name '%sqlquerynotificationservice-%'
i see matching results:
sqlquerynotificationservice-1d50f684-cb16-4feb-a2e1-d53872cd1e23 627637429 null 6 0 sq service_queue 2013-07-10 14:11:14.520 2013-07-10 14:11:14.520 0 0 0 1 [cqs_web].[sqlquerynotificationstoredprocedure-1d50f684-cb16-4feb-a2e1-d53872cd1e23] -2 1 1 1 0 1 sqlquerynotificationservice-1d50f684-cb16-4feb-a2e1-d53872cd1e23 627637429 null 6 0 sq service_queue 2013-07-10 14:11:14.520 2013-07-10 14:11:14.520 0 0 0
so i'm trying remove orphans with:
declare @objname nvarchar(max) declare @msg nvarchar(max) select @objname = name sys.objects name 'sqlquerynotificationservice%' if (@objname 'sqlquerynotificationservice-%') begin print 'found orphan ' + @objname + ', checking conversations' declare @convhandle uniqueidentifier declare conv cursor select conversation_handle sys.conversation_endpoints (nolock) far_service = @objname; open conv; fetch next conv @convhandle; while (@@fetch_status = 0) begin print n' closing conversation ' + cast(@convhandle nvarchar) end conversation @convhandle cleanup; fetch next conv @convhandle; end close conv; deallocate conv; declare @count int; select @count=count(*) sys.services name = @objname if (@count > 0) begin print ' dropping service ' + @objname declare @query nvarchar(200); set @query = n'drop service ' + @objname; exec sp_executesql @query end end go kill query notification subscription go
but that's not clearing them out. how can clear out orphans?
Comments
Post a Comment