I want to identify the orphan mdf and ldf files on the disk and remove them. After searching on the internet, found the script below on MSDN forum. But when I run the script it returns many system-like db files that don't seem to orphan or at least not safe to delete!
Do you know any better way to identify the orphan files? Or can you explain what these db files are referring to (is it safe to delete them)?
For example the files below came in the result of this query:
/* exec sp_configure 'show advanced' ,1 reconfigure exec sp_configure 'xp_cmdshell' ,1 reconfigure */ if object_id('tempdb.dbo.#os_files') is not null drop table #os_files create table #os_files([filename] varchar(2000)) --list all .mdf and .ldf files on the c drive -- you will need to call this again to -- populate the #os_files table, if you have -- db files on other databases eg. d:, e: insert into #os_files exec xp_cmdshell 'DIR C:\*.mdf /b /s' insert into #os_files exec xp_cmdshell 'DIR C:\*.ldf /b /s' delete from #os_files where filename is null update #os_files set filename=rtrim(filename) select os.filename as orphaned_files from #os_files os left outer join master.dbo.sysaltfiles db on rtrim(db.filename) = os.filename where db.dbid is null order by 1