How to identify the db orphan mdf and ldf files

Question

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

Thank you.

Answer 1

The files in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL\MSSQL\Binn\Templates are basic copies of the system databases that are needed for repairing and rebuilding your system dbs in case of problems - http://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx

While you could delete them, you'll be in for a longer recovery period if you need to rebuild the system dbs as you'll need to find the templates from somewhere else.

But other than that, that script appears to do what it proclaims to do. Though, it should be updated to:

select 
    os.filename as orphaned_files 
from 
    #os_files os
    left outer join master.sys.master_files db on rtrim(db.physical_name) = os.filename
where 
    db.database_id is null

as sysaltfiles is deprecated and will be removed from SQL Server in a future version.

Answer 2

Orphan files are database files that were detached and never reattached back. I wouldn't consider the template files as orphan. They are part of the installation and they should be left alone.

If you don't need the others that you find on your disk, then you could simply remove/rename them (with your script or Stuart's addition), because the files that are in use are simply locked by the SQL Server process and you couldn't do anything to them anyway. Just make sure that the SQL Server service is started.

Check if you have more than one SQL instance installed on the machine and, if yes, if there's anyone offline.