T-SQL Query to find Orphan data and log files of SQL Databases

This query will find your server's orphan data and log files of SQL Databases. It helps monitor your SQL database for any orphan files for removing them.

Sometimes, I saw orphan data and log files while dealing with space issues. If you have an associated database in the SQL instance, these files will be known as orphan files. It might occur if you detached a database but did not remove the MDF, LDF files from the database directory.

The following T-SQL statement helps find the orphan data files for you to review and perform clean-ups.

use [master];
set nocount on
 
if object_id('tempdb..#paths') is not null
drop table #paths
create table   #paths ([path_id] int identity (1,1), [data_paths] varchar(255))
insert into #paths ([data_paths])
 
select distinct left([physical_name], len([physical_name]) - charindex('\', reverse([physical_name])) -0)
from sys.master_files
 
if object_id('tempdb..#found_files') is not null
   drop table  #found_files
create table   #found_files ([files] varchar(255), [file_path] varchar(255), [depth] int, [file] int)
 
declare @get_files  varchar(max)
set @get_files  = ''
select  @get_files = @get_files +
'
insert into #found_files ([files], [depth], [file]) exec master..xp_dirtree ''' + [data_paths] + ''', 1,1;
update #found_files set [file_path] = ''' + [data_paths] + ''' where [file_path] is null;
' + char(10) from #paths
exec    (@get_files)
 
select
   'no_associated_database'= [files]
,   'path'          = [file_path]
from 
   #found_files
where
   [files] not in (select right([physical_name], charindex('\', reverse([physical_name])) - 1) from sys.master_files)
   and [files] not in
   (
        'mssqlsystemresource.mdf'
   ,  'mssqlsystemresource.ldf'
   ,  'distmdl.mdf'
   ,  'distmdl.ldf'
   )
   and 
       ([files]  like '%.mdf'
       or [files]  like '%.ldf'
       or [files]  like '%.ndf')