SQL Server Script Error: Database is Already in Use

Question

I have a SQL Server deployment script running from visual studio that gives me the error:

Error SQL01268: .Net SqlClient Data Provider: Msg 1828, Level 16, State 5, Line 1 The logical file name "myDB" is already in use. Choose a different name.

However, when I go into SSMS and try and drop this database, I get the error:

Cannot drop the database 'myDB', because it does not exist or you do not have permission.

Can anyone help me understand where this phantom filename is already stored so that I can delete it?

Thanks.

Some discussion about this error message: social.msdn.microsoft.com/Forums/en-GB/vstsdb/thread/… — Marc B
– Marc B, Commented Sep 23, 2011 at 20:53
@Marc B yes there are some good points there - I assumed that the intention really was to drop the database, but it might not be. — Aaron Bertrand
– Aaron Bertrand, Commented Sep 23, 2011 at 20:55
If it is another user attached to myDB, you can use the MSSMS activity monitor to see which connection/user is on that DB. — Marc B
– Marc B, Commented Sep 23, 2011 at 20:58
@MarcB - I stopped and then restarted SQL Server. Seems like that would've addressed another connection, no? — Jonathan Wood
– Jonathan Wood, Commented Sep 23, 2011 at 21:03

Aaron Bertrand · Accepted Answer · 2011-09-23 20:50:40Z

4

The second error message states that the database cannot be dropped because other sessions are currently connected to it. In order to kick all the users out, you can add this to your deployment script or run it manually before deploying. The USE command is to make sure there isn't a race condition when you are the one who's connected.

USE [master];
GO
ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE myDB;
GO

answered Sep 23, 2011 at 20:50

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Jonathan Wood Over a year ago

User does not have permission to alter database 'myDB', the database does not exist, or the database is not in a state that allows access checks.

Aaron Bertrand Over a year ago

Is that from the deployment script or running manually in SSMS? What version is SQL Server (SELECT @@VERSION;)?

Jonathan Wood Over a year ago

Run manually in SSMS.

Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64)   Feb  4 2011 11:27:06   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Aaron Bertrand Over a year ago

What does this yield? SELECT user_access_desc, is_read_only, state_desc FROM sys.databases WHERE database_id = DB_ID('myDB');? And are you sure you're running as a user with sufficient permissions to alter/drop/create databases?

Jonathan Wood Over a year ago

I get zero rows in the result. Also, when I refresh the list of databases, this database no longer shows. I'm pretty sure I have unlimited permissions here.

|

Community · Accepted Answer · 2017-03-20 10:18:17Z

This error can also occur when trying to generate a database from a script. I was trying to do downgrade a SQL Server 2012 database to 2008 as per this article https://superuser.com/questions/468578/move-database-from-sql-server-2012-to-2008. However, when I ran the script initially, I got this error

The logical file name "emmagarland" is already in use. Choose a different name.

Turns out (after reading this article https://ask.sqlservercentral.com/questions/106577/the-logical-file-name-database-is-already-in-use.html) in my restore script, I hadn't checked and changed the logical file names. I had to ensure the path is correct, and the change the generated name for the log file, else it tries to overwrite the .mdf file with the log and then throws this error. I fixed it by changing the name from xxxxx to xxxxxldf

 CREATE DATABASE [xxxxx] ON  PRIMARY 
 ( NAME = xxxxx', FILENAME = N'C:\Program Files\Microsoft SQL
 Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxxxx.mdf' , SIZE =
 14400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
 ( NAME = N'xxxxxldf', FILENAME = N'C:\Program Files\Microsoft SQL
 Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxxxx_log.ldf' ,
 SIZE = 18560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 GO

Sounds simple but I couldn't work out why this error was occuring when I was creating a brand new database!

Collectives™ on Stack Overflow

SQL Server Script Error: Database is Already in Use

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related