Set read_committed_snapshot On for Databases in an Availability Group

最新推荐文章于 2024-07-19 11:09:26 发布

转载最新推荐文章于 2024-07-19 11:09:26 发布 · 486 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/mowl/p/9364177.html

文章标签：

#数据库

本文介绍了一种在SQL Server中不使用KILL命令而通过移除用户权限来设置数据库为读已提交快照的方法，并提供了具体的步骤和SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

通常为数据库开启read_committed_snapshot需要将数据库设置为单用户模式来关闭数据库上的连接，但是如果数据库在alwayson集群中时，无法运行set single_user，测试发现只要主节点上的数据库没有任何的连接（不需要关心副节点），就能运行set read_committed_snapshot on

那么我们可以通过kill把数据库上的所有连接断掉

use master
go

declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'test'
set @execSql = ''
select @execSql = @execSql + 'kill ' +
CONVERT(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
AND DBID <> 0
AND spid <> @@spid
exec(@execSql)

为了防止程序自动连接，可以先将访问数据库的用户权限去除，在操作完成后，再将用户权限加回去。

我这里实际操作没有使用kill，而是通过去除用户权限，连接就直接断了

USE [test]
GO
DROP USER [test1]
GO

USE [master]
GO
ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

USE [test]
GO
CREATE USER [test1] FOR LOGIN [test1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test1]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [test1]
GO