How to drop a SQL Server database?

最新推荐文章于 2025-07-28 14:57:36 发布

原创最新推荐文章于 2025-07-28 14:57:36 发布 · 2.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #database #数据库 #transactions #query #kill

database 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了在使用SQL Server数据库过程中遇到无法删除数据库的问题，并提供了多种解决方案，包括使用USE MASTER命令、ALTER DATABASE设置为单用户模式以及为SQL Server 7.0用户创建的清除用户会话的存储过程。

今天做数据库实验遇到一个问题—建立的数据库删除不了！问题错误提示如下：

Cannot drop database " DatabaseName " because it is currently in use.寻思了好久，加上在网上搜索资料才解决了。现将与dropa SQL Server database相关的知识记录如下：

The easy answer is to issue the following command in Query Analyzer:

DROP DATABASE 数据库名

However, you will sometimes receive the following error:

Server: Msg 3702, Level 16, State 3, Line 1
Cannot drop the database '数据库名' because it is currently in use.

Hopefully you aren't in the habit of trying to drop production databases; so,more often than not, this error occurs because your current Query Analyzerwindow is actually set to the context of 数据库名. So, I usually recommend gettingrid of extraneous Query Analyzer windows, and using the following commandinstead:

USE MASTER
GO

DROP DATABASE 数据库名

You might still get the error, if other users are connected without yourknowledge. One way to get rid of them immediately:

ALTER DATABASE 数据库名
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

This will drop kick any connections and roll back their transactions, at whichpoint you should be able to drop the database successfully.

However, this leaves out SQL Server 7.0 users, who don't have access to such acommand. Here is a stored procedure I created for 7.0:

CREATE PROCEDURE dbo.clearDBUsers
@dbName SYSNAME
AS
BEGIN
SET NOCOUNT ON

DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)

SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID

PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'

WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)

SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)

SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID

PRINT RTRIM(@cnt)+' processes remain.'
END
END
GO

Sample usage:

EXEC dbo.clearDBUsers '数据库名'

You might have to call the procedure multiple times before it completes theprocess of wiping out existing users.

And because it happens so often, I'm going to suggest again: please make surethat your current Query Analyzer window isn't the elusive process that won't goaway! Make sure you are using the Master database when trying to drop userdatabases!