Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

enabling READ_COMMITTED_SNAPSHOT Expand / Collapse
Author
Message
Posted Monday, March 13, 2006 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 14, 2006 7:45 AM
Points: 2, Visits: 1

Hi,

I'm currently installing service pack 1 to webCT 6 Campus Edition (Virtual Learning Environment) which requires READ_COMMITTED_SNAPSHOT to be turned on.

I have connected to the database at administrator level (owner of db) and executed the following query:

ALTER DATABASE webctdatabase SET READ_COMMITTED_SNAPSHOT ON
Go

The query runs but never finishes.  I have been checking for changes using:

select name,is_read_committed_snapshot_on from sys.database

but the result is still 0 (off).

Interestingly I ran the following query and it completed successfully, instantly.

ALTER DATABASE webctdatabase SET ALLOW_SNAPSHOT_ISOLATION ON

Where am I going wrong?

Chris.

 

Post #265173
Posted Tuesday, March 14, 2006 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:45 AM
Points: 6, Visits: 204

The READ_COMMITTED_SNAPSHOT option can only be set if you are the only connection to the database.  So when you see that command running for a long time, it is waiting for all the other connections to close.

You need to add a termination clause to the command to make sure it doesn't run forever, such as: WITH NOWAIT or WITH ROLLBACK AFTER 30 SECONDS

e.g. ALTER DATABASE webctdatabase SET READ_COMMITTED_SNAPSHOT ON WITH NOWAIT




Post #265573
Posted Wednesday, March 15, 2006 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 14, 2006 7:45 AM
Points: 2, Visits: 1
excellent! thanks very much!
Post #265799
Posted Monday, May 20, 2013 8:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:36 PM
Points: 321, Visits: 1,278
Had Same issue yesterday and i put database in single use mode and ran it again then put it multi user mode. works fine to me

Aim to inspire rather than to teach.
SQL Server DBA
Post #1454575
Posted Monday, May 20, 2013 9:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 42,492, Visits: 35,559
Please note: 7 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1454603
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse