March 2, 2016 at 11:03 am
Hi,
We are not getting any mails. So tried to enable the service broker
ALTER DATABASE [msdb] SET ENABLE_BROKER;
but it is runnning more than 30 min. I can see the spid is blocking by another spid which is sleeping. That spid is not showing from sp_who2 active or select * from sys.dm_exec_requests (SQLAgent - Generic Refresher)
EXECUTE msdb.dbo.sp_sqlagent_refresh_job @job_id = 0x50577E0073C2FF47BA806DB7AB740641
March 2, 2016 at 12:50 pm
Enabling (or disabling) service broker requires an exclusive lock on the database, unfortunately. (In my opinion a major flaw in the design).
You will have to ask everyone to disconnect their sessions, or kill them. Note that your own SSMS is probably also holding open sessions to the database.
Alternatively, use brute force:
ALTER DATABASE db_name SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
WARNING: Thie will immediately terminate all connections to the database, and roll back all open transactions. Do not do this on a production system except during a maintenance window or when the unavailability of the database has been announced well in advance!
March 3, 2016 at 10:19 am
There are no other spids except the SQL Agent Refresher which is running the command
EXECUTE msdb.dbo.sp_sqlagent_refresh_job @job_id = 0x50577E0073C2FF47BA806DB7AB740641.
What it will trying to do? Assuming, if I stop the SQL Agent then I might be able to alter the db easily
March 4, 2016 at 6:44 am
I've never heard of sp_sqlagent_refresh_job. There are a few hits on Google which I'll leave to you to investigate.
If it's a system stored procedure, you can usually still look at the code by checking the system stored procedures tab in msdb. My guess is that it should not run very long; if it does it might be stuck on something. If this is a dev server, try cycling it. (For a prod server, I would not recommend this).
March 4, 2016 at 1:02 pm
It is in sleeping mode. I didn't see that SPID from exec_requests but when I tried to alter it causing bloking
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply