I have recently altered our SQL Server 2008 R2 database to enable SNAPSHOT isolation level and made appropriate changes to hibernate to run all transactions in SNAPSHOT mode. I have however noticed that when I add/delete/alter indexes, the queries which are accessing the underlying table are rolled back and I get this exception:
Snapshot isolation transaction failed in database 'foo' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation
...which is exactly what has happened.
According to https://msdn.microsoft.com/en-us/library/bb933783(v=sql.105).aspx "These statements are permitted when you are using snapshot isolation within implicit transactions." So I tried:
set implicit_transactions on
GO
DROP INDEX blabla
GO
IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off
But the problem still persists. There must be a way to do this in SQL Server?