ALTER DATABASE因数据库'<db_name>'上无法锁定而失败。稍后再试

  • Z时代
  • 2024-01-10
  • 分类：问答

我真的不在乎我用这个测试数据库做什么......它是用于沙盒测试（连接到生产服务器实例的）！我想要做的就是杀死所有连接，如果不要求太多，则删除并创建test_db ....并用一些测试数据恢复。ALTER DATABASE因数据库'<db_name>'上无法锁定而失败。稍后再试

我试过USE [MASTER] RESTORE DATABASE test_DB WITH RECOVERY GO，但得到这个错误：

Msg 3101, Level 16, State 1, Line 1 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

此外，试图
USE [master] ALTER DATABASE test_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;，并得到了错误：

Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'test_DB'. Try again later.
Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

也没
select min(spid) from master..sysprocesses where dbid = db_id('test_DB')，但我的结果集返回NULL

以下是我的代码：

--- Kill Connections 
    USE [master] 
    DECLARE @cmdKill VARCHAR(50) 
    DECLARE killCursor CURSOR FOR 
    SELECT 'KILL ' + Convert(VARCHAR(5), p.spid) 
    FROM master.dbo.sysprocesses AS p 
    WHERE p.dbid = db_id('test_DB') 
    OPEN killCursor 
    FETCH killCursor INTO @cmdKill 
    WHILE 0 = @@fetch_status 
    BEGIN 
    EXECUTE (@cmdKill) 
    FETCH killCursor INTO @cmdKill 
    END 
    CLOSE killCursor 
    DEALLOCATE killCursor 
    --Drop and Create 
    USE [master] 
    GO 
    IF EXISTS (SELECT name FROM sys.databases WHERE name = N'test_DB') 
    DROP DATABASE [test_DB] 
    GO 
    USE [master] 
    GO 
     CREATE DATABASE [test_DB] ON PRIMARY 
     (NAME = N'test_db_Data', FILENAME = N'\\some_place\d$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_DB.mdf' , SIZE = 125635136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%) 
     LOG ON 
     (NAME = N'test_db_Log', FILENAME = N'E:\SQLLogs\test_DB.ldf' , SIZE = 1064320KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%) 
     GO 
    ALTER DATABASE [test_db] SET ....

回答：

如果仍有打开的连接，数据库不能脱机。

此外，请确保您的连接未使用该数据库（USE master），然后使用ALTER DATABASE的WITH ROLLBACK IMMEDIATE选项使其脱机。

回答：

你知道谁连接了吗？

SELECT 
    DB_NAME(dbid) as 'DBName' 
    , loginame as 'Login' 
    , COUNT(dbid) as 'Connections' 
FROM 
    sys.sysprocesses 
WHERE 
    dbid > 0 
GROUP BY 
    dbid 
    , loginame

以上是 ALTER DATABASE因数据库'&lt;db_name&gt;'上无法锁定而失败。稍后再试 的全部内容， 来源链接： utcz.com/qa/258082.html

回到顶部