Allow Data Definition Commands in Transactions

You can use certain data definition language commands, such as create table, grant, and alter table, in transactions by setting the ddl in tran database option to true.

If ddl in tran is true in the model database, you can issue the commands inside transactions in all databases created after ddl in tran was set to true in model. To check the current settings of ddl in tran, use sp_helpdb.

Warning!   Use data definition commands with caution. The only scenario in which using data definition language commands inside transactions is justified is in create schema. Data definition language commands hold locks on system tables such as sysobjects. If you use data definition language commands inside transactions, keep the transactions short.

Avoid using data definition language commands on tempdb within transactions; doing so can slow performance to a halt. Always leave ddl in tran set to false in tempdb.

To set ddl in tran to true, enter:

sp_dboption database_name,"ddl in tran", true

Then execute the checkpoint command in that database.

The first parameter specifies the name of the database in which to set the option. You must be using the master database to execute sp_dboption. Any user can execute sp_dboption with no parameters to display the current option settings. To set options, however, you must be either a system administrator or the database owner.

These commands are allowed inside a transaction only if the ddl in tran option to sp_dboption is set to true:

You cannot use system procedures that change the master database or create temporary tables inside transactions.

Do not use these commands inside a transaction: