delete older detached database files

Question

delete older detached database files

sqlfriend

SSC Guru

Points: 52794
More actions
November 9, 2012 at 2:11 pm

#284616

We have some older databases that on a server. Then we detached them. And decide after a while we will remove them
But now I forgot which are the mdf, and ldf files for those databases.
Is there a way to find out what mdf files are detached in the data folder, then I can remove them?
Thanks

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

sqlfriends (11/9/2012)
We have some older databases that on a server. Then we detached them. And decide after a while we will remove them
But now I forgot which are the mdf, and ldf files for those databases.
Is there a way to find out what mdf files are detached in the data folder, then I can remove them?
Thanks

If the database is attached and the sql service is running you can't delete the files. The OS will tell that the file is in use and can't be deleted. I would of course make a full backup of all your databases AND that folder before you delete anything. Then next time make sure you write it down (or put the list of files in your admin database for easy retrieval later).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thanks, I know if attached, the files are used by sql service and cannot be deleted. But if detached, then the files may be not used and can be deleted.

Of course I will double check the data files names before I delete them.

But for this post, I would like to know if there is a way to find out what are detached files in the data directory. ( except the method to see if they can be deleted to find out)

Thanks,

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

I think I just figure out one way is to compare the result of

select database_id, type_desc, DB_NAME(database_id),name, physical_name from sys.master_files

where physical_name not like '%.ldf'

and database_id not in (1,2,3,4,5)

With the query result, I do a comparison with the files in data directories, those are not in the query result, they are probably the detached db files.

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

You can check select * from sys.master_files. That will give you a list of files that ARE in use. Then you could get a list of files in the directory. The files that are in the directory and not in use from any database are not attached.

--EDIT--

spelling.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dan-572483 SSCoach Points: 15870 More actions · Answer 5

For each Database in SSMS run

sp_helpdb <database name>

or

USE <database name>

SELECT * FROM sys.database_files

This will help you build a list of all data & log files that ARE attached.

If you're not sure which detached data files go with which detached log files (because they don't follow a naming convention that makes it obvious), you can attach a data file, then run one of the commands above to learn which are the log file(s).

At that point, if you're 100 percent certain you don't need the DB anymore, you can run

drop database <database name>

and the data & log files will be deleted.

sqlfriend SSC Guru Points: 52794 More actions · Answer 6

sqlfriend

SSC Guru

Points: 52794

November 9, 2012 at 2:50 pm

#1557817

Thanks, I tried the same thing, it works great.

dan-572483 SSCoach Points: 15870 More actions · Answer 7

Thanks Sean! select * from sys.master_files is a better way to get a list of all active data & log files.