November 30, 2015 at 12:11 pm
Comments posted to this topic are about the item Orphaned Database Files
December 8, 2015 at 1:02 am
WARNING: Don't run this script on a server with multiple instances as it will select all SQL files from other instances (hope these instances are running)
Wilfred
The best things in life are the simple things
December 9, 2015 at 3:51 am
Good idea to check on orphaned files!
You could also correlate the two tables by joining them, and simplify the script:
SELECT MF.physical_name, FL.Filepath
FROM #FileList FL
LEFT OUTER JOIN sys.master_files MF
ON FL.Filepath = MF.physical_name
WHERE MF.physical_name is null
And perhaps add a few exceptions to not display items in the trash can and templates directory,
as well as the 'mssqlsystemresource' data and log files, which do not show up the output of sys.master_files, but should not be removed:
SELECT MF.physical_name, FL.Filepath
FROM #FileList FL
LEFT OUTER JOIN sys.master_files MF
ON FL.Filepath = MF.physical_name
WHERE MF.physical_name is null
AND NOT ( PATINDEX('%Templates\%.[ml]df%' ,FL.FilePath) > 0
OR PATINDEX('%$RECYCLE.BIN%' ,FL.FilePath) > 0
OR PATINDEX('%mssqlsystemresource%',FL.FilePath) > 0
)
Then, the whole script becomes:
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
DROP TABLE #FileList
CREATE TABLE #FileList(FilePath VARCHAR(255))
-- Alter the line of code below to suit you server
-- 'Where /R D:\ *.mdf *.ldf *.ndf'
INSERT INTO #FileList (FilePath)
EXEC xp_cmdshell 'Where /R D:\ *.mdf *.ldf *.ndf'
SELECT MF.physical_name, FL.Filepath
FROM #FileList FL
LEFT OUTER JOIN sys.master_files MF
ON FL.Filepath = MF.physical_name
WHERE MF.physical_name is null
AND NOT ( PATINDEX('%Templates\%.[ml]df%' ,FL.FilePath) > 0
OR PATINDEX('%$RECYCLE.BIN%' ,FL.FilePath) > 0
OR PATINDEX('%mssqlsystemresource%',FL.FilePath) > 0
)
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
DROP TABLE #FileList
December 9, 2015 at 6:28 am
Looks interesting. I'll have to try it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply