"Snapshot isolation transaction failed accessing database 'RedGateMonitor'

rajesh_mehta

Hi,
All of a sudden , we started getting this error in SQL monitor.
"Snapshot isolation transaction failed accessing database 'RedGateMonitor' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation."

TimG2

I ran into the same issue after moving my RedGate database.  I ran the following commands on my database named RedGateMonitor.

ALTER database RedGateMonitor SET SINGLE_USER WITH ROLLBACK immediate;

SELECT name,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on 

from sys.databases ;

---

ALTER DATABASE RedGateMonitor SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE RedGateMonitor SET ALLOW_SNAPSHOT_ISOLATION ON

--

 SELECT 

name,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on 

from sys.databases ;

ALTER database RedGateMonitor SET multi_USER

I then had to stop the SQL Monitor Base Monitor service (MonitorBaseDeploymentService) in Services.msc on my monitoring server and restart it to reestablish the connection.