5

There are various statements and system procedures that can't be executed within a transaction, with an error message of "not allowed..." (e.g., create database) or "cannot be executed..." (e.g., exec sp_addrole).

Is there a rule or set of rules that would indicate whether a given procedure/statement isn't allowed in a transaction? Is there a listing thereof?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Marc L.
  • 3,296
  • 1
  • 32
  • 42

1 Answers1

1

Not an official Microsoft resource, but the most comprehensive list I've found so far...

  • ALTER DATABASE
  • BACKUP LOG
  • BACKUP DATABASE
  • RESTORE LOG
  • RESTORE DATABASE
  • RECONFIGURE
  • UPDATE STATISTICS
  • LOAD DATABASE
  • LOAD TRANSACTION
  • CREATE DATABASE
  • DISK INIT
  • DROP DATABASE
  • System stored procedures that alter the MASTER database

The final item is a pretty broad category... but you can reasonably predict what types of system stored procedures would be altering the MASTER database... essentially anything that functions at the server level instead of the database level.

Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
  • From the top of my head, I also think that Full Text Indexes or Catalogs cannot be altered inside a transaction – MaxiWheat Dec 21 '12 at 02:04