从多用户切换单用户简单,麻烦的是从单用户切换到多用户。可能会总是出现提示有用户连接的情况。
我试过很多其它办法,都遇阻。
以下代码,完美解决。快,准,狠。
推荐。
f you try to access the database which is already in the Single-User mode, you need to close all the connections to the database first, otherwise you will get an error message:
Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'DatabaseName' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
The following query kills the processes accessing the database:
-- Create the sql to kill the active database connections declare @execSql varchar(1000), @databaseName varchar(100) -- Set the database name for which to kill the connections set @databaseName = 'DatabaseName' 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) GO
Then you should be able to bring the database back to Multi-User mode as usual:
ALTERDATABASE'DatabaseName'SET MULTI_USER