Poet vs SQLQueryNotificationService
Hello lovely tdwtf-ers,
this is not really a coding question as more of a DBA one, but I was hoping someone might know.
I have a MS SQL Server database on which a SQLQueryNotificationService queue is running every second. It is trashing the windows event log because it cannot find the stored procedure it's trying to call.
"The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-9957b324-e637-41af-8d9f-8deba5f3db6c]' running on queue 'PRACTICE.dbo.SqlQueryNotificationService-9957b324-e637-41af-8d9f-8deba5f3db6c' output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-9957b324-e637-41af-8d9f-8deba5f3db6c'."
When I try to drop the bar-steward it tells me:
Msg 3716, Level 16, State 14, Line 1
The queue 'dbo.SqlQueryNotificationService-9957b324-e637-41af-8d9f-8deba5f3db6c' cannot be dropped because it is bound to one or more service.
Okay, I thought, I am dufus. I checked I was the DBO, even ran a ALTER AUTHORIZATION ON DATABASE::DB TO ME which completed successfully. Still no joy. Then I thought... well what service could possibly be it. Broker perhaps, therefore
ALTER DATABASE PRACTICE SET DISABLE_BROKER
ALTER DATABASE PRACTICE SET NEW_BROKER
ALTER DATABASE PRACTICE SET ENABLE_BROKER
Then I thought ... run
select * from sys.dm_qn_subscriptions
see if this helps somehow. Weirdly, I don't see the damn queue there. I thought I should.
So what do I need to do to get rid of that annoying bugger? Any ideas?
Oh, I tried a kill query notification subscription ALL which did run just fine, except it didn't fix my issue.
Would it be possible to track what is trying to run that SP creating one with that name, and logging something?
Sadly no profiler on there and it's a customer system where I only have some partial powers on the PC. I suspect it's an ARCSERVE DB Sync that calls it.. but I can't say for sure.
Oh... The %^£$%^£(£%^*!!! I got it.
Apparently Service Broker had hung shutting down when I tried to disable it. It's fine now. New Broker, no insanity.
I love it when that happens.
taskkill -f -im stupid_program.exe
E: another instance is already running
Oh look, it's still alive wtf?
That, Sir, just made me laugh heartily. This has happened to me so many times and its always as infuriating as the first time.
What I would love to have is a way to easily claim to be a debugger app, hook into a non-responding program, then suicide and take it down with me....
Just obliterate all references to the program in memory. You thought you had CPU access? NOT ANY MORE BUDDY.
Sloosecannon does not hold any responsibility for unfortunate occurrences due to following instructions given in forum posts. Use at your own risk. Do not taunt happy fun ball.
Yes, I like pulling the rug, continent, planet, universe, and reality out from under my misbehaving programs. They generally learn to behave after that. At least that's what I tell myself.
Can SIGKILL be caught, and if so, WHY?
Shouldn't SIGKILL be a kernel message to drop the process from existence?
Not necessarily caught, but if you're totally deadlocked, the SIGKILL doesn't even get processed. I've only really had those "this thing should be dead but I hardlocked it totally and completely so it might not even respond to a kill" processes fail to die so I assume it's related to that...
Caused by IO or device drivers, evidently.
i see this a lot of the time from programs that were written for one version of .net but are actually running on another. Backwards compatibility my behind.
Sublime Text tends to do that to me when working on stuff mounted over
sshfs. I'm guessing it gets stuck in some I/O operation and deadlocks so badly even the kernel gives up. And leaks memory. 99% of all of reboots of my work machine is Sublime Text being an ass.