sql query to find drive letters of all user datadrives which dont include any ldf files.

Question

Hi All,

Can you help me to find all the user database drives (all drives which contains mdf and ldf) which doesn't include even one log ldf file.

SELECT DISTINCT LEFT([physical_name], 1)
FROM sys.master_files
WHERE (physical_name like '%mdf' OR physical_name like '%ndf' ) AND
database_id > 4

minus

SELECT DISTINCT LEFT([physical_name], 1)
FROM sys.master_files
WHERE (physical_name like '%ldf' ) AND
database_id > 4

Let me brief with an example

datafiles present in E,F,G
Log files F

Desired result is E and G

Thanks,
ACDBA

Answer 1

Hi @ACDBA ,

Please try the following.

SQL

SELECT DISTINCT LEFT([physical_name], 1)  
FROM sys.master_files  
WHERE RIGHT(physical_name, 4) IN ('.mdf','.ndf')   
	AND database_id > 4  
EXCEPT  
SELECT DISTINCT LEFT([physical_name], 1)  
FROM sys.master_files  
WHERE RIGHT(physical_name, 4) = '.ldf'  
	AND database_id > 4;  

Answer 2

Hi @ACDBA

maybe I don't understand your question/problem

Your first query gives you just the location of all mdf/ndf files and that is already your desired result... maybe it is too early for me

I hope my answer is helpful to you,

Your
Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!