ENABLE_BROKER, DISABLE_BROKER run indefinitely

3.75
Average: 3.8 (4 votes)
Your rating: None

If it seems ENABLE_BROKER and DISABLE_BROKER will run forever, it will be probably true. This mostly happens when you run:

ALTER DATABASE [databaseName] SET ENABLE_BROKER

and

ALTER DATABASE [databaseName] SET DISABLE_BROKER

These operations need exclusive database lock. Therefore use these statements:

ALTER DATABASE [databaseName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

and

ALTER DATABASE [databaseName] SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE

Funny thing is it can occur when you're creating new database from statement generated on other server (I have database [databaseName] on ServerA, generated CREATE statement in SSMS and used it for creating new database on ServerB by putting into SSMS – the batch hanged at the DISABLE_BROKER statement altough it was brand new database and nobody (and nothing) could access it).

Keywords: MSSQL, ENABLE_PROKER, DISABLE_BROKER, indefinitely, forever