We are using Octopus Deploy to perform model-driven deployments to our SQL Server databases based on the DacPac created in a Visual Studio Database project. One of the post-deployment scripts was hanging indefinitely:
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'MyDatabase' AND is_broker_enabled = 1)
ALTER DATABASE [MyDatabase] SET ENABLE_BROKER
This is caused by the script essentially waiting for everyone else’s connection to end, which isn’t likely to happen by chance.
To solve this problem, the script needs to be run in single user mode:
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'MyDatabase' AND is_broker_enabled = 1)
ALTER DATABASE [MyDatabase] SET ENABLE_BROKER
ALTER DATABASE [MyDatabase] SET MULTI_USER
And here is the real version that takes the Ocotpus Deploy database name variable from our project variables page.
ALTER DATABASE [$(DatabaseName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '$(DatabaseName)' AND is_broker_enabled = 1)
ALTER DATABASE [$(DatabaseName)] SET ENABLE_BROKER
ALTER DATABASE [$(DatabaseName)] SET MULTI_USER