在READ_COMMITTED_SNAPSHOT ON期间挂起 [英] Hanging during READ_COMMITTED_SNAPSHOT ON
问题描述
在我们的任何数据库上发出以下命令时，它只会挂起
.
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
我意识到所有连接都需要查询窗口需要关闭，这是我认为的情况，或者至少我们正在重置
web服务器仍然可以看到问题。我能够修复它的唯一方法是完全停止并重新启动数据库服务器，然后
发出命令并立即返回。
这是一个痛苦但必须在下班后完成。是否有办法在系统使用时发出命令
，可能只需将数据库脱机（而不是服务器上的所有其他Dbs）时间
然后使用脚本将其返回使用？
When issuing the below command on any of our databases, it just hangs
forever.
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
I realize that all connections expect the query window need to be
closed and that is the case I think, or at least we are resetting the
web server and still see the issue. The only way I have been able to
fix it is to completely stop and restart the database server, then
issue the command and it returns immediately.
This is a pain though and has to be done after hours. Is there a way to
issue the command while the system is in use, possibly taking just that
database offline (and not all other Dbs on the server) for a short time
and then returning it back to use using just scripting?
推荐答案
pb648174（go ** **@webpaul.net）写道：
pb648174 (go****@webpaul.net) writes:
当我们在任何数据库上发出以下命令时，它只会挂起
。
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
我意识到所有连接都需要关闭查询窗口，这是我认为的情况，或者至少我们正在重置
Web服务器看到这个问题。我能够解决它的唯一方法是完全停止并重新启动数据库服务器，然后发出命令并立即返回。
When issuing the below command on any of our databases, it just hangs
forever.
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
I realize that all connections expect the query window need to be
closed and that is the case I think, or at least we are resetting the
web server and still see the issue. The only way I have been able to
fix it is to completely stop and restart the database server, then
issue the command and it returns immediately.
您是否使用sp_who查看
活跃的数据库的其他连接？
您可以使用
ALTER DATABASE db SET SINGLE_USER立刻回滚
作为一种让每个人都出局的方式。完成后别忘了将它设置回
多用户。
-
Erland Sommarskog， SQL Server MVP， es****@sommarskog.se
联机丛书SQL Server 2005
http： //www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Did you use sp_who to see what other connections to the database that
were active?
You can use
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
as a guick way to get everyone out. Don''t forget to set it back to
multi user when you are done.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
问题间歇性地发生，所以当出现错误时我无法真正执行
sp_who语句。从理论上讲，它怎么能阻止
？在为读取操作启动
事务之前，难道不能只获得该行的版本吗？
The problems happen intermittently, so I can''t really execute the
sp_who statement when there is an error. Theoretically, how could it
ever block? Shouldn''t it just get the version of the row before the
transaction started for the read operation?
哎呀，错误的信息，没关系
Whoops, wrong message, nevermind
这篇关于在READ_COMMITTED_SNAPSHOT ON期间挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
