Autosys installation: SQL command: READ_COMMITTED_SNAPSHOT ON appears to be hung

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• Workload Automation Agent

Issue/Introduction

We are at step of the Windows upgrade documentation and have issued the Database command (alter database AEDB set read_committed_snapshot on).
However, the command has been running for over 24 hours and appears to be hung. 
Any idea what the problem may be and/or how to kill the command? 

 

Environment

Installation of CA-WAAE 11.3.6 SP7 on Windows with SQL Server.

This problem might happen with any SQL Server release and any Workload Automation Autosys Edition release

Cause

To avoid database deadlocks, we recommend that you set the READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT option to ON, as follows: 

    1.Stop all applications that are connected to the AEDB database. 
     2.Log in as the sa user and run the following SQL command: 
                         alter database AEDB set read_committed_snapshot on 

You can perform this task either before or after the upgrade. 

Oftentimes, the above T-SQL will just hang forever. 99% of the time this is because there are still active connections to the target database (AEDB in this case).

Resolution

This below article describes different methods to turn this option "on" in SQL Server

https://willwarren.com/2015/10/12/sql-server-read-committed-snapshot/
 

You can apply the change and rollback any active transactions at the same time by running:

ALTER DATABASE AEDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

Another extract from this URL

Having needed to enable it on an active application with active database connections in the past, I’ve found that option 2 works well as long as you do it during off-hours 

Here’s a script that can do this (using AEDB again as example):

-- Switch over to master to avoid hanging connection problems
USE master
GO

/**
 * Cut off live connections
 * This will roll back any open transactions after 30 seconds and
 * restricts access to the DB to logins with sysadmin, dbcreator or
 * db_owner roles
 */
ALTER DATABASE AEDB SET RESTRICTED_USER WITH ROLLBACK AFTER 30 SECONDS
GO

-- Enable RCSI for AEDB
ALTER DATABASE AEDB SET READ_COMMITTED_SNAPSHOT ON
GO

-- Allow connections to be established once again
ALTER DATABASE AEDB SET MULTI_USER
GO

-- Check the status afterwards to make sure it worked
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE [name] = 'AEDB'
GO

If that last SELECT returned 1 then you are done

Additional Information

See also this link  https://stackoverflow.com/questions/232333/how-long-should-set-read-committed-snapshot-on-take/13343078

CA-WAAE upgrade guide: https://docops.ca.com/ca-workload-automation-ae/11-4-2/en/upgrading/ae-upgrade/upgrade-ca-workload-automation-ae-r11-3-or-a-later-release-to-the-current-release