How to Enable Read-Committed Snapshot in XenDesktop

This article describes how to enable the Read-Committed Snapshot option on an SQL Server database.

Requirements

The information in this article requires you to have access to the target database using SQL Server Management Studio.

Background

Citrix recommends that the SQL Server Read-Committed Snapshot option is enabled on any XenDesktop database. This is particularly beneficial for sites that will include large numbers of machines or users, or that will experience significant load at certain times (for example, large number of logons at the start of the working day).

Citrix Studio and Read-Committed Snapshot

If Citrix Studio is used to create a new XenDesktop database, or upgrade an existing one, in many cases it automatically enables Read-Committed Snapshot at that time, however this might not always be possible, in which case it is necessary to manually enable the option.

There are also two cases where Citrix Studio never automatically enables Read-Committed Snapshot. These are:

• An existing mirrored database

• An existing database forming part of an SQL Server 2012 availability group

In XenDesktop 7 where multiple databases might be in use, Read-Committed Snapshot should be enabled on all of them.

Manually Enabling Read-Committed Snapshot

Directions for enabling Read-Committed Snapshot on a simple database with both SQL Server Management Studio 2008 and 2012 are given in the following sections along with instructions for dealing with the preceding two special cases not automatically handled by Citrix Studio.

而不管SQL Server管理学生io 2008 or 2012 is used, the act of enabling Read-Committed Snapshot aborts any in progress transactions being executed by XenDesktop services. The services simply retry the aborted transactions and continue without interruption to site availability; however, it is recommended that this operation is performed away from times of peak activity.

The example SQL commands included in this article assume that the target database is called CitrixSiteDB. Replace this name as appropriate for the actual database being used.

Using SQL Server Management Studio 2008

There is no simple mechanism in SQL Server Management Studio 2008 to either check the current state of, or enable, the Read-Committed Snapshot option.

• To check the current state of Read-Committed Snapshot, open a query window and execute the following command:
  select is_read_committed_snapshot_on from sys.databases where name= 'CitrixSiteDB';

  A value of 1 indicates that Read-Committed Snapshot is already enabled and no change is required.

• Use the following command to enable Read-Committed Snapshot:
  alter database [CitrixSiteDB] set read_committed_snapshot on with rollback immediate;

Using SQL Server Management Studio 2012

SQL Server Management Studio 2012 has the ability to set the Read-Committed Snapshot option directly. On the target database, selectProperties>Optionsand scroll down toIs Read Committed Snapshot On. Set the option toTruewhere required.

Cases Not Handled by Citrix Studio

Where the target database is already mirrored, or forms part of an SQL Server 2012 availability group Citrix Studio never automatically enables Read-Committed Snapshot. These cases must be dealt with manually as described in the following section:

Mirrored Databases

Read-Committed Snapshot cannot be enabled directly on a database that is already mirrored. The mirror must be broken, Read-Committed Snapshot enabled, and the mirror reinstated.

1. Using SQL Server Management Studio, on the current Principal database of the mirror, selectTasks>Mirror… and record the current mirroring configuration shown in the displayed dialog. This is important because SQL Server Management Studio does not remember these settings once the mirror is broken.

2. Select删除镜像. The Mirror database of the pair changes to state “Restoring…”. Do not change this database in any way.

3. On what was the Principal server, enable Read-Committed Snapshot for the target database as previously described for the version of SQL Server Management Studio being used.

4. SelectTasks>Mirror…on the Principal database, then “Configure Security…” and proceed through the dialogs to re-establish the mirror. It is not necessary to back up the database to recreate the mirror, simply reentering the appropriate configuration causes mirroring to resume to the original Mirror database. The Mirror database changes from state “Restoring…” to “Mirror, Synchronized / Restoring…” once the mirror is reinstated.

Databases in SQL Server 2012 Availability Groups

Citrix Studio never automatically enables Read-Committed Snapshot on an existing database that forms part of an SQL Server 2012 availability group. However, to do this manually, you must follow the instructions given earlier for enabling Read-Committed Snapshot using SQL Server Management Studio 2012.