March 12, 2009 at 8:30 am
We lost a sql server disk array. Had to be re-raided. Then system disk and registry was restored from tape. Then I copied over a master db, started sql in single user and restored master. Then started sql and restored the model, msdb & the production DBs. DBCC Checkdb clean on all DBs. System back to users with no data loss. I scheduled by weekly dbcc job to run last night and it failed with "Service Broker message delivery is not enabled in this db" message. I compared sys.databases against this system's sister test box and see only tempdb is broker_enabled on the rebuilt live system. What happened? How did that change?
Next I went to Surface Area Config and got error message "SQL Server WMI is not available on localhost." Do I need to re-install sql server? Should I try to service-pack it? I though we were golden after the db restores. This is a Lawson single server system, sql server & Lawson app on the same box. The app works fine. Am I sitting on a time bomb?
March 26, 2009 at 7:09 am
FYI - Guess I'll never know why my restored DBs had the Service Broker turned off. All I had to do was alter database MSDB set enable_broker. My DBCC query uses DB Mail to send the dbcc results. The Service Broker has to be on in MSDB to use DB Mail. Stopping the SQL Server Agent stops all processes in MSDB so the alter database could work.
As for WMI, I don't really know what that is, but when the NT guys restored the system drive, WMI was a seperate option, much like the registry is a seperate option during a restore. They didn't restore it so at the moment I can't use Surface Area Config. This server is being replaced in May so its not a big deal to me, just something to remember for the next time.
June 25, 2012 at 8:35 am
Randy, Thanks It worked for me.
I had a similar situation with send email option in the job.
July 18, 2013 at 3:23 am
My alter database MSDB set enable_broker command has been running for over 10 minutes. Is that normal? I'm still waiting for it to finish...:-(
August 26, 2016 at 8:06 am
Hey man...
Make Sure msdb is in Single User Mode
Make sure you set the msdb database to single user, or this statement just hangs:
alter database msdb set restricted_user with rollback immediate
and, set it back to multi user when you're done
alter databsae msdb set multi_user with rollback immediate
..or Stop the SQL Server Agent which is what Microsoft deems Best Practices.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply