Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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')

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.

share|improve this question
up vote 11 down vote accepted

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.

share|improve this answer
"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. – Petter Brodin Jan 12 '14 at 13:18

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.


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

share|improve this answer

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 )


Begin tran

---Statements goes here

commit tran

share|improve this answer
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. – Max Vernon Jul 30 '14 at 4:09
"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. – Michael Green Jul 30 '14 at 10:52

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.