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')