Unable to drop database in SQL Server 2012

Question

I have created a sample database earlier and now I want to delete that database, but it is not getting deleted. I searched online but I didn't find any solution which is working.

Using T-SQL, I tried:

USE [Sample]

ALTER DATABASE [Sample]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE [Sample]

Using the GUI, I am getting this below error:

I closed existing connection then also this is happening and this is my local machine. Please help me here!

just test DROP DATABASE Sample; – Hasan Fathi Jan 27 '18 at 11:03 — Hasan Fathi, Jan 27 '18 at 11:03
this statement not working? – Hasan Fathi Jan 27 '18 at 11:03 — Hasan Fathi, Jan 27 '18 at 11:03
@Hasan Fathi. I tried that also, it didn't worked!. – Indranil Jan 27 '18 at 11:04 — Indranil, Jan 27 '18 at 11:04

score 3 · Accepted Answer · answered Jan 27 '18 at 11:06

3

use this code:

USE MASTER 
GO

ALTER DATABASE Sample 
SET multi_user WITH ROLLBACK IMMEDIATE
GO


ALTER DATABASE Sample
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE Sample
GO

answered Jan 27 '18 at 11:06

Hasan Fathi

5,610
4
42
60

score 1 · Answer 2 · answered Jan 27 '18 at 11:07

Close your SSMS , open a new instance and then try the following, Copy paste the whole thing into a new query window and execute at once:

USE [master];            
GO

ALTER DATABASE [Sample]   --<-- go back into Multi-user mode
SET MULTI_USER;
GO

ALTER DATABASE [Sample]   --<-- Will disconnect everyone 1st
SET SINGLE_USER              -- and will leave the database in single user mode
WITH ROLLBACK IMMEDIATE;
GO

USE [Sample];                -- quickly grab that single connection before any 
GO                           -- other process does

USE [master];                -- Connect to master db,  connection
GO                           -- This also means disconnect Sample DB 

DROP DATABASE [Sample]       -- At this point there should be no active connections
GO                           -- to this database and can be dropped

score 0 · Answer 3 · answered Jan 27 '18 at 12:26

Another way to delete the Database (without coding)

In Microsoft SQL Server Management Studio, Right-click the Database that you want to delete (In your case, Sample Database) and then Select Properties.
Select the 'Options' Page.
Under the other option, Find the 'Restrict user' option under 'State'.
Change it value MULTI_USER to SINGLE_USER
Then Press Okay and Try again (Or right-click the database again and click Delete)

score 0 · Answer 4 · answered Jul 18 '19 at 09:27

0

Use this Code should help.

ALTER DATABASE [dbname]
SET SINGLE_USER --or RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [dbname];
GO

answered Jul 18 '19 at 09:27

Darren G

193
2
9

Unable to drop database in SQL Server 2012

4 Answers4

Linked