Hello All,
I have another problem with setting READ_COMMITTED_SNAPSHOT on SQL Server 2005 now.
The problem is:L
In SQL Server 2005, when running following statement:
ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON
It takes so long time to fnish. Actually I have been waiting 25 minutes for it, it is still not yet finished.
Do you know why?
Thanks a lot
Mel
When issuing the below command on any of our databases, it just hangsforever.ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;I realize that all connections expect the query window need to beclosed and that is the case I think, or at least we are resetting theweb server and still see the issue. The only way I have been able tofix it is to completely stop and restart the database server, thenissue the command and it returns immediately.This is a pain though and has to be done after hours. Is there a way toissue the command while the system is in use, possibly taking just thatdatabase offline (and not all other Dbs on the server) for a short timeand then returning it back to use using just scripting?
View 10 Replies View RelatedI asked this awhile back and didn't get any answers. I'm trying again -- maybe more people have started using the snapshot isolation features. This seems like a pretty fundamental question that is not adequately addressed by the documentation.
It is documented that READ_COMMITTED_SNAPSHOT is more efficient than ALLOW_SNAPSHOT_ISOLATION. Is the efficiency hit incurred merely by turning ALLOW_SNAPSHOT_ISOLATION on or does one actually need to be running a lot of SET TRANSACTION ISOLATION LEVEL SNAPSHOT queries?
In other words, if I set both READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION on but run 99% of my queries as SET TRANSACTION ISOLATION LEVEL READ COMMITTED, have I incurred a significant hit for turning ALLOW_SNAPSHOT_ISOLATION on?
Thanks