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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy