ansaurus

Question

SQL Server service broker reporting as off when I have written a query to turn it on

Answer 1

A:

Do you use a normal SQL instance, including Express, or are you using User Instances? ie. your connection string uses "AttachDbFilename" option? On user instances because the MDF is attached each time the instance is created, the Service Broker gets disabled each time because attach and restore operations always disable the broker in the database.

Remus Rusanu 2009-08-22 00:01:19

Normal instance (enterprise edition).

dotnetdev 2009-08-22 00:04:41

You can check the `is_broker_enabled` column value in `sys.databases` to confirm that broker is enabled in the database. If is not you need to run ALTER again and perhaps check what component may disable it.

Remus Rusanu 2009-08-22 02:51:45

Answer 2

A:

Ditch the "with rollback immediate" and try running the statement again.

Does it hang? If so kill any other sessions that are in the database and are blocking the session trying to alter the database.

Here's the script I mentioned in my comment.

use master
go
declare @parent int
declare @cmd varchar(100)

set @parent = 53

while exists (select * from sys.sysprocesses where spid = @parent and blocked <> 0)
begin
    select @cmd = 'kill ' + cast(blocked as varchar(10))
    from sys.sysprocesses
    where spid = @parent 

    print @cmd

    exec (@cmd)
end

mrdenny 2009-08-22 04:55:19

Yes it does hang without the rollback immediate. I'll try killing the sessions, but I'm the only person logged in/where can I kill sessions?

dotnetdev 2009-08-22 13:22:42

Then something else is connected to the database. Close the Object Explorer if needed and any other query windows that you have open. Query the sys.sysprocesses DMV to see what other processes are using the database (or look to see what processes are blocking your ALTER DATABASE command) and use the KILL T/SQL command to kill those processes. You'll need to kill them all to get the ALTER DATABASE command to take effect. I'll post a script I use which should help.

mrdenny 2009-08-23 20:37:28

ansaurus

tags:

views:

answers:

SQL Server service broker reporting as off when I have written a query to turn it on

related questions