Proper way to delete a SQL Server Database

Question

Hi,

I have a SQL Server Database named 'ToBeDeleted' that I would like to delete permanently.

Can anyone here tell me if executing the following script would be an appropriate way to do this? ....

USE master
GO
DROP DATABASE ToBeDeleted
GO

Thank you in advance,
Paul

Answer 1

Yes that will drop the database in most cases. The one situation it will not is when there are active processes in the database. In this case you will get an error.

If this occurs, you can run this brutal command:

ALTER DATABASE ToBeDeleted SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Now DROP DATABASE should succeed.

Answer 2

Hi @Paul Kraemer ,

yes, it is an appropriate way.
https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql?view=sql-server-ver15

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 3

Hi Erland and Andreas.

Thank you for your responses. As Erland mentioned, I did get an error related to the database being in use. I am certain that there are not connections to this database that are important (or that would make me not want to delete it). Before I try the brutal method Erland mentioned, I am curious to know what is connected to this database. Do you know if there is a way I can see the active connections?

Thanks again and best regards,
Paul

Answer 4

Hi @Paul Kraemer ,

Agree with Erland. In addition, you can also use below T-SQL to check the user who connected to this database and the session ID.

SELECT DB_NAME(dbid) AS DBName,  
loginame, spid as sessionID  
FROM    sys.sysprocesses  
where DB_NAME(dbid)='ToBeDeleted'  

By the way, you can also delete this database from SSMS UI as below screenshot.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 5

Thank you Erland, Cathy, and Andreas - you all were a very big help!

I appreciate it!!

Best regards,
Paul