ALTER DATABASE statement not allowed within multi-statement transaction

Question

I've downloaded the AdventureWorks based In-memory sample from here, and followed all the steps described in the accompanying doc. However, when I try to run the script in SQL Server Management Studio, I get the error message:

ALTER DATABASE statement not allowed within multi-statement transaction

The error points to line 9, which is:

IF NOT EXISTS (SELECT * FROM sys.data_spaces WHERE type='FX')
 ALTER DATABASE CURRENT ADD FILEGROUP [AdventureWorks2012_mod] 
 CONTAINS MEMORY_OPTIMIZED_DATA
GO

Since this is (more or less) official Microsoft documentation, I'm assuming it's something I'm doing wrong, but I can't figure out what it is.

Answer 1

No, you're not doing anything wrong. I got the same thing. I solved it by breaking the sample up into multiple scripts and running each section of the script sequentially, in its own query window, instead of as one big script. This worked in my case because I'm always running these samples in an isolated VM (not on a production server!) and transaction handling is unnecessary since I'm the only one here.

Looking at the script again today more closely, there is no transaction handling defined explicitly, but perhaps you pasted the script into a query window that already had an active transaction, or created a new query window that automatically added BEGIN TRANSACTION; / COMMIT TRANSACTION; statements.

I also pointed out a couple of other potential gotchas in this blog post.

• Petter Brodin: "perhaps you pasted the script into a query window that already had an active transaction" That seems to have been the problem, because when I ran the whole query in a new window, it worked.

Answer 2

I agree with @AaronBertrand you aren't doing anything wrong. This wouldn't be the first time I have seen a Microsoft script with a bug in it. Realistically with as many scripts as they publish I would be surprised not to see any.

Specifically the problem is that ALTER DATABASE is not allowed in a transaction at all. You can see the BOL reference here: Transact-SQL Statements Allowed in Transactions

In fact even as simple a script as this fails with the same error.

BEGIN TRANSACTION
ALTER DATABASE AdventureWorks2012 SET READ_WRITE
COMMIT

As Aaron said, remove the transaction handling (or at least the ALTER DATABASE statement from the transaction) and you should be fine.

Answer 3

Use "Go" to separate the transactions. That will solve the issue. ( Its easy than run one by one.) Also can change the Isolation level ( Not tested )

SET TRANSACTION ISOLATION LEVEL SERIALISABLE
Begin tran
---Statements goes here
commit tran
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

• Hannah Vernon: You may want to test your code before posting when others have already identified that ALTER DATABASE cannot be performed within a transaction. Setting the isolation level to SERIALIZABLE has no effect on this.
• Michael Green: "GO" isn't a SQL statement. It's an instruction to SSMS to submit the preceeding statement(s) to SQL Server as a batch. You can change this if you're feeling brave: Tools -> Options -> Query Execution -> SQL Server. Many batches can be submitted within a single transaction.