Dropped database, but mdf & ldf files still exist

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 2008 Forums

SQL Server Administration (2008)

Dropped database, but mdf & ldf files still exist

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-11-01 : 08:14:56

hi guys,
been migrating databases accross drives and dropped the old databases, however when i check on the c drive,
C:\Program Files\Microsoft SQL Server\MSSQL10.mydb\MSSQL\DATA
the mdf and ldf files still exist.

Do i delete them or what ?
thanks
Ehi

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-01 : 08:45:19

easy way to check them is to try to rename the files. If they are in use, you wont be able to rename them.

You cal also query sys.master_files to check whether those files are being used.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-11-01 : 09:58:19

ok thanks.

I have deleted them, I wonder why sql server didnt just do the job.

I also ran

SELECT
DB_NAME([database_id]) AS N'database_name'
,[name] AS N'logical_name'
,[physical_name]
FROM sys.master_files
WHERE [database_id] > 4;

and the name didnt show up

thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-01 : 10:32:40

The database was offline when you dropped it? If so, it's documented that the files will not be deleted.

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 10:47:40

Are you sure they were the files the database was using when it was dropped - maybe it had been moved/restored and these were left over.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-11-01 : 10:54:04

quote:
Originally posted by GilaMonster

The database was offline when you dropped it? If so, it's documented that the files will not be deleted.

--
Gail Shaw
SQL Server MVP

Yes You are right Gail, i took it online first, just to make sure i wasnt making a mistake.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-11-01 : 10:54:37

quote:
Originally posted by nigelrivett

Are you sure they were the files the database was using when it was dropped - maybe it had been moved/restored and these were left over.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yes very very sure Nigel.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-01 : 15:03:14

From Books online

quote:
Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

--
Gail Shaw
SQL Server MVP

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-11-02 : 05:54:51

thanks buddy,
its all done and dusted now, new db is online and working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources