今天公司SQL Server数据库无意间变为SINGLE_USER模式了,而且使用如下语句切换回MULTI_USER失败:
ALTER DATABASE [MyDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE
报错:
Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'MyDB' 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.
该错误提示是有其它用户在使用数据库,没办法只好排查是谁:
1)通过sys.sysprocesses或者sys.dm_exec_sessions,或者存储过程sp_who,然后用KILL命令把会话切断
select * from sys.sysprocesses where spid > 50 And dbid=DB_ID ('MyDB')
SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID ('MyDB')
2)如果上面还是行不通,再检查sys.dm_tran_locks,然后用KILL命令把会话切断
select * from sys.dm_tran_locks where resource_database_id= DB_ID ('MyDB')
kill 148