This database is in single-user mode, which prevents the database from being deleted.

Jul 4, 2023#SQLServer755

AI Translation

This post is translated from Chinese into English through AI.View Original

AI-generated summary

Today, there was an error while trying to delete a database. The error message indicated that the database was in single-user mode and still connected, making it unable to be deleted even after restarting SQLServer. After searching on Baidu, a solution was found. It involved creating a stored procedure in the master database to disconnect all user connections and then calling it to resolve the issue.

Today, when deleting a database, an error kept occurring, roughly meaning: This database is in single-user mode and is still connected, so it cannot be deleted (even if SQL Server is stopped and restarted).

After searching on Baidu, I found a solution, which is backed up here:

USE [master]
GO
 
/****** Object:  StoredProcedure [dbo].[killspid]    ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
  --Create a stored procedure to disconnect all user connections.
  create   proc   [dbo].[killspid]   (@dbname   varchar(20))  
  as  
  begin  
  declare   @sql   nvarchar(500)  
  declare   @spid   int  
  set   @sql='declare   getspid   cursor   for    
  select   spid   from   sysprocesses   where   dbid=db_id('''+@dbname+''')'  
  exec   (@sql)  
  open   getspid  
  fetch   next   from   getspid   into   @spid  
  while   @@fetch_status<>-1  
  begin  
  exec('kill   '+@spid)  
  fetch   next   from   getspid   into   @spid  
  end  
  close   getspid  
  deallocate   getspid  
  end  
     
 
GO

First, create a stored procedure in the master database to kill all connections, and then call it using:

use   master   
exec   killspid   'problematic database name'

After executing, you can perform operations on the database.