CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
beshr
beshr

Reputation: 293

error: ALTER DATABASE statement not allowed within multi-statement transaction

I'm trying to run the following query, but it give me the following error message:

ALTER DATABASE statement not allowed within multi-statement transaction.

the query is:

 ALTER DATABASE TSQL2012
 SET READ_COMMITTED_SNAPSHOT ON;

and as shown in the pic: any idea why?

enter image description here

Upvotes: 7

Views: 33239

Answers (3)

Pavlo Tsybulivskyi
Pavlo Tsybulivskyi

Reputation: 344

In my case similar issue was solved by switching to master db, probably because you can't use a db and alter it at the same time.

Upvotes: 2

Abhinav Vanam
Abhinav Vanam

Reputation: 51

I'll make things easier for you.

The thing is there is an uncommitted transaction there. So, the sql server just wants you to commit that first and then run this query.

If you dont want that transaction then just close that query window, it will ask if you wanna save it or no. Then open new one and then run the query you want.

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

A multi-statement transaction is one that is either created, explicitly, by a BEGIN TRANSACTION statement, or one that has been created by use of the Implicit Transactions mode - that is, a statement has executed and, before the next statement executes, the transaction count is still greater than 0.

So this implies that, on the connection on which you're trying to execute your code, the transaction count is already greater than zero.

You should execute either ROLLBACK (safest if you don't know what was already done in the transaction) or COMMIT (safest for preserving data changes already performed), and then you can execute the ALTER DATABASE statement.

Upvotes: 18

Related Questions