unable to drop and create database in sql server

Question

I'm working with SQL Server 2008 and I can't seem to do drop and create a database.

I've tried a few different ways but I always end up failing to drop or trying to "use" before it seems to be created.

My current attempt looks like this.

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

The GO were suggested on a MS forum as a way to stop some issues that occur when selecting databases.

With this I currently get the output (with a ore existing database of the same name) of:

Msg 3702, Level 16, State 4, Line 3
Cannot drop database "test" because it is currently in use.
Msg 1801, Level 16, State 3, Line 1
Database 'test' already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 2
There is already an object named 'staff_type' in the database.

With the last 2 lines repeated for every table in my database.

Answer 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

Answer 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".

Answer 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.

Answer 4

ALTER DATABASE test1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE test1 SET OFFLINE;

DROP DATABASE test1

Try this inside stored procedure

Answer 5

This will give you all the current connections:

select spid, hostname, [program_name], open_tran, hostprocess, cmd
from master.dbo.sysprocesses 
where dbid = db_id('your_database_name')

Then you could use a t-sql cursor to execute kill @spid, where the value for @spid is from the previous query.

Answer 6

If you are getting the above error while using Master. then you need to close SQL Server Management Studio completely and again open it and connect to it and run your above query.....

Hope,it'll works.....

Answer 7

If you're running into this after having programmatically interacted with the database via ADO.NET and SqlConnection, and you're sure you've closed each of your connections after use and there's really nobody else in there, you might be getting tripped up by connection pooling. Try this C# code to clear the pool prior to connecting to master or another database to issue the DROP DATABASE command on your database:

SqlConnection.ClearPool(yourSqlConnectionObject);

Answer 8

You need to close all the query window using this database also you might need to restart the SQL Server completely. This might solve your problem.

Answer 9

Along with mr_eclair's answer above, I would like to add:

• All Query window must be closed where the currect db is selected.
• Another option is make the db in single user mode.>> it will kill all the other users processes
• set OFFLINE WITH ROLLBACK IMMEDIATE. it will make the db in offline mode and bring it back
• use sp_who2 to know the users using the current db. and killthe required spids

Answer 10

If you have SQL files open that have previously queried the DB you are trying to drop, these will prevent drop. As mention above. Closing these resolved issue for me

Answer 11

I faced this type of problem when working with Sql Server Management Studio. After many days of googling and experiments, i finally found an issue.

NB: You ought to firstly create a drop and create table script for this table, if not you will not have your table

1-First create only yours tables with theirs coresponding foreign keys.

2-Create a visual diagram with these table (Sql express-Databases-Databasename-DataBase Diagram-Right click on it and select new database diagram)

3-Add the required datatables on diagram and create the relation between these datatables with corresponding foreign keys added during the creation of tables

4-Then saved your Database

In the case that you have forget to add a given field in a datatable, you can easily drop and create your datatables, to do this, follow these steps:

1-Open the Database diagram of the corresponding database

2-delete all the relationships which exist between the old table to which you want to add some field and others tables

3-then delete the corresponding table from diagram(right click on the table , then select delete table from the datatable)

4-Save the diagram (Ctrl +S)

5-go to the table that you want to drop and create

6-Right click on the table and select( Script table as then select drop and create then go to new Query editor windows), this will script your table in new table, at this time you can modify it to your need, exemple with and old and new same table

Old table

        USE [DatabaseName]
      GO

         /****** Object:  Table [dbo].[Administrateur]    Script Date:  10/11/2016 2:06:04 PM ******/
      DROP TABLE [dbo].[Administrateur]
     GO

      /****** Object:  Table [dbo].[Administrateur]    Script Date: 10/11/2016 2:06:04 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

   CREATE TABLE [dbo].[Administrateur](
[AdministrateurID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Surname] [nvarchar](max) NULL,
[Phone] [nvarchar](max) NOT NULL,
[Username] [nvarchar](max) NOT NULL,
[Password] [nvarchar](max) NOT NULL,
[Sexe] [nvarchar](max) NOT NULL,

 CONSTRAINT [PK_Administrateur] PRIMARY KEY CLUSTERED 
 (
[AdministrateurID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =    OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 GO

Now the NEW SAME TABLE WITH 3 NEW FIELDS(Email, Image and Salt)

   USE [DatabaseName]
   GO

    /****** Object:  Table [dbo].[Administrateur]    Script Date: 10/11/2016 2:06:04 PM ******/
  DROP TABLE [dbo].[Administrateur]
  GO

   /****** Object:  Table [dbo].[Administrateur]    Script Date:    10/11/2016 2:06:04 PM ******/
  SET ANSI_NULLS ON
  GO

  SET QUOTED_IDENTIFIER ON
  GO

   CREATE TABLE [dbo].[Administrateur](
[AdministrateurID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Surname] [nvarchar](max) NULL,
[Phone] [nvarchar](max) NOT NULL,
[Email] [nvarchar](max) NOT NULL,
[Username] [nvarchar](max) NOT NULL,
[Password] [nvarchar](max) NOT NULL,
[Image] [nvarchar](max) NOT NULL,
[Sexe] [nvarchar](max) NOT NULL,
[Salt] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Administrateur] PRIMARY KEY CLUSTERED 
  (
   [AdministrateurID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =    OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Then in the page of the modified Datatable, Press Execute. It will not execute for the first time and will write some errors encountered, but don't care and just press Execute in second time. At this time, it will execute and write the success message at the bottom of the document.Then select the database and click on Refresh (or press F5), he will update your Database's tables in some computer or you will need to restart the program before seing the updates in others computers(I don't know why, so don't ask me to explain).

Go back now to the diagram and dd the updated table and then connect these(this) table(s) to the tables which has any relation with it.

Hope that this will save the time of someones.

I don

Answer 12

I know I´m late to the game. But here is how I do this in one step. This was happening so often I did´t want to do this in many steps so I combined it to one single step.

DECLARE @databaseName VARCHAR(30); 
DECLARE @resource_type_to_kill VARCHAR(30); 
DECLARE @processIdToKill INT;

SET @databaseName = 'yourDatabaseName' 
SET @resource_type_to_kill = 'DATABASE'

DECLARE @TempSession TABLE
(
    ProcessIdToKill INT,
    DatabaseName VARCHAR(100),
    Request_Mode VARCHAR(100),
    HostName VARCHAR(100),
    LoginTime VARCHAR(100),
    LoginName VARCHAR(100),
    Status VARCHAR(100),
    Reads VARCHAR(100),
    Writes VARCHAR(100)
);
INSERT @TempSession
SELECT DISTINCT
    session_id,
    name,
    request_mode, 
    host_name,
    login_time,
    login_name,
    status,
    reads,
    writes
FROM    sys.dm_exec_sessions
    LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id =  sys.dm_tran_locks.request_session_id
    INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE   resource_type = @resource_type_to_kill
AND name = @databaseName
ORDER BY name

--SELECT * FROM @TempSession --Debugging

SELECT @processIdToKill = ProcessIdToKill FROM @TempSession

--SELECT @processIdToKill --Debugging

--Run kill for the process that is using the database to be dropped.
DECLARE @SQL nvarchar(1000)
SET @SQL = 'KILL ' + CAST(@processIdToKill as varchar(4))
PRINT 'Killing the process'
EXEC (@SQL)

--And then drop the database
DECLARE @DropSQL nvarchar(1000)
SET @DropSQL = 'DROP DATABASE ' + @databaseName
PRINT 'Dropping the database'
EXEC (@DropSQL)

If there are many processes that are using the database you´ll just have to run this multiple times.

Answer 13

Totally random thought here. But if you have a SQL DB project open in Visual Studio, its open-ness will occupy processes even if you aren't taking any actions or have open query windows in SSMS.

This was the issue in my case. Closing Visual Studio completely, allowed me to drop the database with no issue.

Answer 14

For linux try restarting mssql.server

sudo systctl mssql-server.service

then DROP Databse "DatabseName"