SNAPSHOT ISOLATION question/issue for sql server 2008 SP3 with security update

• richard.vanveen

  Right there with Babe

  Points: 773

  More actions

  July 22, 2014 at 8:08 am

  #288869

  I'm running Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)

  Aug 22 2012 19:25:47

  Copyright (c) 1988-2008 Microsoft Corporation

  Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

  During a helix upgrade on our production server, they ran the following command in one of the scripts:

  SELECT name as [DB_NAME], snapshot_isolation_state_desc, compatibility_level, user_access_desc, recovery_model_desc, * from sys.databases order by name.

  This somehow caused our login trigger to fail with the following message, becuase the login trigger writes data to the 'monitordb':

  Snapshot isolation transaction failed accessing database 'monitordb' because snapshot isolation is not allowed in this database. User ALTER DATABASE to allow snapshot isolation.

  I ran the alter database monitordb set allow_snapshot_isolation on, and then the login failures stopped.

  The login trigger has been running ok for multiple months without any issues.

  I was wondering if anyone has any idea why we'd get the error, and if there are any suggestions/opinions on whether we should keep the option on, or turn it back off.

  Thanks very much for any replies and insights. This is very strange.