March 13, 2006 at 7:08 am
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.
March 14, 2006 at 8:16 am
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
March 15, 2006 at 2:25 am
excellent! thanks very much!
May 20, 2013 at 8:50 am
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
May 20, 2013 at 9:49 am
Please note: 7 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply