ALTER DATABASE statement sometimes not run on Azure SQL Database

Question

Hello to all. I'm new to Azure.
I have created an Azure SQL Database instance and run ALTER DATABASE statements on SQL Database query editor.

The statements have not run. Seemingly the statements have not even started. But sometimes run and completed.
I can't find the reason why somtimes go well and someteimes not.

My ALTER DATABASE statements contain "SET READ_COMMITTED_SNAPSHOT OFF" which requires any other connections are not active, so I opend only one query editor and shut down SQL Server Managemente Studio. But nothing changed. Someteimes go well and somtimes not.

The statements are below:

ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE [dbname] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [dbname] SET ANSI_NULLS OFF
ALTER DATABASE [dbname] SET ANSI_PADDING OFF
ALTER DATABASE [dbname] SET ANSI_WARNINGS OFF
ALTER DATABASE [dbname] SET ARITHABORT OFF
ALTER DATABASE [dbname] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [dbname] SET AUTO_SHRINK OFF
ALTER DATABASE [dbname] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [dbname] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [dbname] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [dbname] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [dbname] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [dbname] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [dbname] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [dbname] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE [dbname] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [dbname] SET READ_WRITE
ALTER DATABASE [dbname] SET MULTI_USER

My questions are:

• Threre are something which is blocking ALTER DATABASE statements?
• Can I find them before running the statements?
• If there are someting blocking, how can I remove them?

Thanks.

Answer 1

The database may be "in transition" after applying those statements like READ_WRITE, MULTI-USER and some time may be needed, before other ALTER DATABASE statements can be run.

In addition, ALTER DATABASE statements require locks on the database. Try to kill all connections before running the statements except the current connection.

DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'

FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id <> @@SPID
ORDER BY c.connect_time ASC