unable to drop and create database in sql server

Solution 1:

We usually get this error If You've opened any query window with connection to this database, so make sure you close all your opened query windows connected to db which you're trying to drop.

Don't use the database which you're trying to drop. use master to drop any user database that is a good practice.

Make sure No other process is attach to the database you're trying to drop.

EXEC sp_who2
--Run kill spid for each process that is using the database to be dropped.
kill <<processid>> -- Kill 57

Use EXEC sp_who2 and check the DBName column, your database name should not appear in the list, if it appears kill the process using kill <<processid>> then try to drop.

Try this code.

use master
GO

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'test')
DROP DATABASE [test]
GO

CREATE DATABASE [test]
GO

use [test]
GO

Solution 2:

1. Right-click on the database and select "Delete" (or left-click it and press the 'del' key).
2. When the 'Delete Object' dialog appears, make sure to checked "Close existing connections" (see below, it's unchecked by default).
3. Press "OK".

Solution 3:

try this:

use master;
GO

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO
.....

This will rollback any transaction which is running on that database and brings SQL Server database in a single user mode.

Solution 4:

ALTER DATABASE test1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE test1 SET OFFLINE;

DROP DATABASE test1

Try this inside stored procedure