SQL Packager not keeping the READ_COMMITTED_SNAPSHOT SQL option ON
Published 07 February 2013
Unfortunately it is not possible to configure SQL Packager to create a database setting READ_COMMITTED_SNAPSHOT
. When you package a database for a new install, the transaction isolation level will always be unspecified, which will more than likely set it to "read committed".
How to fix
There are a couple of workarounds:
- Use a post SQL Script:
If you run the package from the command line, you can specify/postsql:<filename>
. You could use a sql file that contains theALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON
, which will be run after the database is created. - Edit the SQL Package and add the code to set the user options.
To do this, output the package as a C# project, then in the PackageExecutor class add the following code after the dboptions were set:
sqlCommand.CommandText = "Alter database " + m_DatabaseName + " SET ALLOW_SNAPSHOT_ISOLATION ON"; sqlCommand.ExecuteNonQuery(); sqlCommand.CommandText = "Alter database " + m_DatabaseName + " SET READ_COMMITTED_SNAPSHOT ON"; sqlCommand.ExecuteNonQuery();
This sets the isolation level to 'read committed snapshot' confirmed by checking DBCC USEROPTIONS
after the database is installed..