DELETING DATABASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

DELETING DATABASE

Author

Topic

gmkrrishnan
Starting Member

3 Posts

Posted - 2012-11-07 : 08:32:16

Hi all,
while am deleting the database, getting the following error message
"Cannot drop the database 'abc', because it does not exist or you do not have permission", but there is no database in the name 'abc' and while creating the same database again, getting an error like "The logical file name 'abc' is already in use. Choose a different name". now what can i do for delete that logical file named as abc completly. Any one give me the suitable solution.
Thank U

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-07 : 08:51:46

Are you logged in as a user who has sysadmin role? If not, you may not be seeing the database even if it exists. Assuming that you do have sysadmin privileges, run this query to see what files and names are in use.

EXEC sp_msforeachdb 
	'select ''?'' as databasename,[type_desc],[name],[physical_name] 
	 FROM ?.sys.database_files';

If that does not help, look in the path where you are trying to create the data and log files and see if a file with the same name already exists there.

gmkrrishnan
Starting Member

3 Posts

Posted - 2012-11-09 : 08:54:53

HI sunitabeck,
I've checked all those tables still am getting the same message so kindly tel me any other solution.
even though, if am create new database with the name which one could'n't be created already, it shows same error message again yar...
Thank You. . . !

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 09:58:10

Do you have the right permissions to create the database? Run this query to see if you are a sysadmin or dbcreator

SELECT
	ServerRole = rp.name,
	PrincipalName = SP.name
FROM sys.server_role_members rm
	INNER JOIN sys.server_principals rp
		ON rm.role_principal_id = rp.principal_id
	INNER JOIN sys.server_principals SP
		ON rm.member_principal_id = SP.principal_id

gmkrrishnan
Starting Member

3 Posts

Posted - 2012-11-15 : 02:38:24

Sorry, even though am getting the same error, here i show you what i did in my side
--------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
CREATE DATABASE abc
ON (NAME = abc,
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\abc_data.mdf',
SIZE = 2048,
MAXSIZE = UNLIMITED)
LOG ON (NAME = abc,
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\abc_log.ldf',
SIZE = 1048,
MAXSIZE = UNLIMITED)
COLLATE Latin1_General_BIN
GO
ALTER DATABASE abc SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE abc
SET ARITHABORT ON
GO
ALTER DATABASE abc
SET QUOTED_IDENTIFIER ON
go
----------------------------------

and an error is :
--------------------------------
Msg 1828, Level 16, State 5, Line 1
The logical file name "abc" is already in use. Choose a different name.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'abc', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'abc', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'abc', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
--------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources