No Managed Connections. Steps to enable Read Committed Snapshot Isolation- MSSQL

No Managed Connections. Steps to enable Read Committed Snapshot Isolation- MSSQL

Enabling Read Committed Snapshot Isolation in MS SQL server
In MS SQL environments enabling 'Read committed Snapshot isolation (RCSI)' is the first step in handling application server performance and crash issues.

What is RCSI?  

Since databases handle multiple transactions in parallel, there has to be a mechanism to handle concurrent reads and writes in the database. For example, if one transaction is running a update query and other transaction is executing a select query then, whether the select query should be able to see the newly updated value or the old value? Or should the select query transaction wait till the update query transaction completes? These are some of the questions that are answered based on the concurrency model that is being used. There are 2 concurrency models.
1. Optimistic concurrency control  
2. Pessimistic concurrency control  

In pessimistic model, transactions lock the rows that they are reading or modifying so that other transactions have to wait till this transaction completes to access the rows.

In optimistic model, the rows are not locked. Two transactions can simultaneously read/update a row. Only if there are any conflicting updates identified, then one of the transactions is rolled back. Since there is no locks involved in this model, this performs much better than the former.  

Postgres database by default has optimistic model. SQL server on the other hand was originally designed using pessimistic model but due to performance problems in it an option was later provided in SQL server to enable optimistic concurrency control. This is called read committed snapshot isolation.

How to verify / enable RCSI  

To check whether RCSI has been enabled:  

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE [name] = '<db_name>'
If this returns 1 then RCSI is enabled. If not, follow the below instructions to enable it.  

Instructions to enable RCSI:   

This process might take considerable amount of time to complete. Please do this on a weekend / during off hours as it requires downtime.  
   
1. Shut down the ServiceDesk Plus service. If any other application (maybe reporting tools) connects to servicedesk database, you need to stop them too.  

2. Please login to SQL Management studio and execute the below queries...  

      i. take the database offline
USE master 
GO 
ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE 
GO

      ii. enable read committed snapshot  
ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE 
GO

      iii. bring database back online  
USE master
GO
ALTER DATABASE <db_name> SET ONLINE 
GO

3. Please verify whether RCSI is enabled using the query mentioned at the top. You may start ServiceDesk Plus now.  


Reference:  

Understanding RCSI :  

How to take database offline and bring it back online :   
        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

                      • How to Change default password on PGSQL database

                        Please do follow the below steps which would assist you in changing the default Postgres password.  1. Stop the application services.   2. Please take a trimmed backup <OR> take a Snapshot of  the Server 3. Open Command Prompt with Admin Privileges ...

                      • Phone number need to be mandatory in 8.1 for contacts

                        Kindly follow the steps below to create the FAFR, 1. Access admin Module 2. Select the request template option and click the edit option the required request template the script needs to be applied on 3. Select the Fields and Forms Rule tab and paste ...

                      • Change the default font-size and font-family while sending emails from SCP

                        Kindly try the following steps below, Access the database and initiate the query below select * from globalconfig where category = 'rta'; It will list out the global config categories and from the list check for the  globalconfigid for font -size and ...

                      • How to configure SAML with Azure AD

                        This guide will help us configure SAML for users who want to use Azure AD as their IdP and also give you insights on a few issues that you might run into while configuring SAML in an Azure Environment. In an ideal environment, customers will have an ...

                      • No ManagedConnections - Steps to increase pool size

                        From the logs we were able to identify the error trace as [org.apache.catalina.authenticator.NMSFormAuthenticator]|[WARNING]|[68]|: Unexpected error while forwarding to login page : {0}|javax.servlet.ServletException: ...

                        Related Products