I have a lost entry in my sys.service_queues table.
When i do
select * from sys.service_queues where name like '%SqlQueryNotificationService-%' select * from sys.objects where name like '%SqlQueryNotificationService-%'
I see the corresponding 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 am trying to remove orphans with:
DECLARE @ObjName nvarchar(max) DECLARE @Msg nvarchar(max) select @ObjName = name from sys.objects where name like 'SqlQueryNotificationService%' IF (@ObjName like 'SqlQueryNotificationService-%') BEGIN print 'Found a orphan ' + @ObjName + ', checking for conversations' DECLARE @ConvHandle uniqueidentifier DECLARE Conv CURSOR FOR SELECT conversation_handle FROM sys.conversation_endpoints WITH (nolock) WHERE far_service = @ObjName; OPEN Conv; FETCH NEXT FROM Conv INTO @ConvHandle; WHILE (@@FETCH_STATUS = 0) BEGIN print N' Closing a conversation ' + CAST(@ConvHandle AS nvarchar) END CONVERSATION @ConvHandle WITH CLEANUP; FETCH NEXT FROM Conv INTO @ConvHandle; END CLOSE Conv; DEALLOCATE Conv; DECLARE @count int; SELECT @count=COUNT(*) FROM sys.services WHERE name = @ObjName IF (@count > 0) BEGIN print ' Dropping service ' + @ObjName DECLARE @query as nvarchar(200); SET @query = N'drop service ' + @ObjName; EXEC sp_executesql @query END END GO KILL QUERY NOTIFICATION SUBSCRIPTION ALL GO
But that does not clear them. How can I clean my orphans?
source share