SQL Server: Long Wait on ENABLE_VERSIONING for ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON

Question

ALTER DATABASE <database_name> SET ALLOW_SNAPSHOT_ISOLATION ON; on a 4TB database in SINGLE_USER mode doesn't finish (I had to stop it after 6 hours)

Session is waiting on ENABLE_VERSIONING

No IO was detected.

No blocking sessions exist.

Happens on both:

SQL Server version: Developer Edition 14.0.3500.1 (SQL Server 2017)

SQL Server version: Developer Edition 16.0.4205.1 (SQL Server 2022)

Answer 1

That does not sound exactly normal.

Although, the description for ENABLE_VERSIONING says:

Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.

So when you say that there is no IO, is that on the server in general or only for this session? And if the database is in SINGLE_USER, there cannot really be any update operation pending... Or could SINGLE_USER be the problem and preventing that update from complete? The update could be a log redo or similar. SINGLE_USER is not required for ALLOW_SNAPSHOT_ISOLATION.

I would also run DBCC CHECKDB just be sure that there is any corruption.

What you could try on SQL 2022 is to first enable accelerated database recovery. This will also enable versioning, but with the version store in the database. (This feature is not available on SQL 2017.)