How to enable RCSI (Read Committed Snapshot Isolation) level for a Database with Database Mirroring

Hi SQL Guys – By default we’re having Read Committed isolation level on in our databases. Some times its required to reduce the locking mechanism at database level, RCSI (Read committed snapshot isolation) level can reduce blocking and deadlocking issues caused by lock contention. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. The read-committed snapshot isolation level provides the following benefits:

  1. SELECT statements do not lock data during a read operation. Read transactions do not block write transactions and vice versa.
  2. Since SELECT statements do not acquire locks in most cases, the number of locks required by a transaction is reduced, which reduces the system overhead required to manage locks.
  3. The possibility of blocking is significantly reduced.
  4. SELECT statements can access the last committed value of the row while other transactions are updating the row without getting blocked.
  5. The number of blocks and deadlocks is reduced.
  6. Fewer lock escalations occur.

But its a bit difficult to enable RCSI at database which is participating in database mirroring. Below are the steps that will make your task easier :

  • Disable the log backup for a database on which you wants to enable RCSI.
  • Disconnect all users from database by taking out all web\Extra-net servers from HLB (in case when application back end is SQL db) OR you can kill the connections on that db, if still connections are visible through sp_who2 then disable the lo-gins through which connections are coming. RCSI can be enable w\o taking the db into single user mode.
  • To enable the RCSI you need to break the db mirroring but don’t panic you need not to restore full db backup here, database engine will take care of it. To break the mirroring fire below command :

                    ALTER DATABASE <db_name> SET PARTNER OFF

  • Now you ca enable RCSI by using below command :

                        Use <db_name>

                         go

                   ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON

  • Isolation level must be set now, verify it through below command, here col. name is_read_committed_snapshot_on = 1, shows RCSI is set.

         select name, is_read_committed_snapshot_on from sys.databases where name = <db_name>

  • Re-configure the db mirroring, it’s not require to restore full db backup here. Mirroring will only transfer left over T-Logs.
  • Wait till db mirroring is not synchronized for db, then enable T-Log backup for database.

Stay tune for more stuffs !

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: