Finding Orphaned Data Files

So Kendra Little's post about how dropping a database that is in an offline state will not clean up the MDF and LDF files got me thinking. I hadn't really considered this before, and wanted an easy way of seeing if I was wasting space with large MDF files that we thought we dropped years ago...a query across CMS should fit the bill.

Here's what my code does:

• Extracts the folder path and data file names for all databases on the instance, from sys.master_files.
• For each unique path that is used for storing data files, use xp_cmdshell to get all file names stored there.
• Find any MDF or NDF files in that list that aren't mentioned in sys.master_files.

Here's the code:

DECLARE @files TABLE (pth VARCHAR(500), fname VARCHAR(500));
DECLARE @cmdOutput TABLE (txt VARCHAR(1000) NULL);
DECLARE @cmd VARCHAR(1000), @MDFpath VARCHAR(500);
SET NOCOUNT ON;

INSERT INTO @files
SELECT
        LEFT(physical_name,LEN(physical_name)-PATINDEX('%\%',REVERSE(physical_name))+1),
        RIGHT(physical_name,PATINDEX('%\%',REVERSE(physical_name))-1)
FROM sys.master_files WHERE type = 0;

DECLARE pathcurs CURSOR FOR SELECT DISTINCT pth from @files
OPEN pathcurs
FETCH NEXT FROM pathcurs INTO @MDFpath
WHILE @@FETCH_STATUS = 0  
BEGIN
        SET @cmd = 'dir ' + @MDFpath + ' /a:-d /b';
        INSERT INTO @cmdOutput EXECUTE master..xp_cmdshell @cmd;
        FETCH NEXT FROM pathcurs INTO @MDFpath
END  
CLOSE pathcurs
DEALLOCATE pathcurs

SELECT c.txt AS DetachedDataFiles FROM @cmdOutput c
LEFT JOIN @files f ON UPPER(c.txt) = UPPER(f.fname)
WHERE UPPER(c.txt) LIKE '%.[M,N]DF%' AND f.fname IS NULL;


Note, in SQL 2005 you'll probably see references to distmdl.mdf and mssqlsystemresource.mdf, which are the data files for the Distribution and Resource databases, respectively. You can ignore these, or alter the script above to ignore them.