I have a client-server app running in Windows Server 2008R2 using/interacting SQL Server 2012.
After reading app's documentation as well as feedbacks on internet from those who already accomplished it with the same app (but with SQL Server 2008), I've switched app's SQL Server
READ_COMMITTED_SNAPSHOT isolation level in app's databases to
This resulted in immediate users' complaints about errors from MS SQL Server:
Соединение с сервером баз данных разорвано администратором (The connection with database server was terminated by administrator) Microsoft Microsoft SQL Server Native Client 11.0: Communication link failure HResult=80004005, HResult=80004005
which stopped after switching OFF these changes.
What is the most strange is that these errors were on reading/viewing operations like generation of reports, opening detalization of entities from opened reports, opening read-only docs, etc.
Eventvwr hadn't registered any insightful events if any at all.
Having hard times to understand why does the switching on
READ_COMMITTED_SNAPSHOT ON result in breaking/refusing new connections by MS SQL Server?
The changes were made by scripts:
USE [master] GO ALTER DATABASE [DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE [DB_NAME] SET ALLOW_SNAPSHOT_ISOLATION ON --OFF ALTER DATABASE [DB_NAME] SET READ_COMMITTED_SNAPSHOT ON --OFF ALTER DATABASE [DB_NAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE GO
per each database with different database name
And the results were controlled by script:
select name, snapshot_isolation_state_desc, is_read_committed_snapshot_on from sys.databases
Just to stress that SQL Server worked but rejected requests for reading additional details on already shown reports.