Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT isolation level in app's databases to ON.

This resulted in immediate users' complaints about errors from MS SQL Server:

enter image description here

which is:

Соединение с сервером баз данных разорвано администратором   
(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 ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT ON result in breaking/refusing new connections by MS SQL Server?

UPDATE:

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.

share|improve this question
    
Did you use the WITH ROLLBACK IMMEDIATE option in conjunction with ALTER DATABASE? –  Martin Smith Oct 16 '13 at 8:00
    
@Martin-Smith , yes, please, see "UPDATE" in my question –  Gennady Vanin Novosibirsk Oct 16 '13 at 8:47
    
Does this behaviour occur even after restarting the client app? Seems like it might be related to connection pooling. –  Jon Seigel Oct 16 '13 at 13:20
    
@Jon-Siegel , I did not have time to check anything. There are 60-80 users per a database and many of them started to complain all at once, so I just reversed these changes back with restarting SQL and app servers and complaints stopped. I've just made a few screens and noted the context which was quite surprising - the problems were on reading/viewing attempts without any modifications –  Gennady Vanin Novosibirsk Oct 17 '13 at 2:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.