Quick & Dirty way to identify orphan files

7. January 2010 by admin.

Here is a quick and dirty script to identify orphan database files - that is, files that are no longer in the system catalog. It uses several undocumented stored procedures and is not particularly clean SQL, but it works.

To do: come up with a version that uses LogParser to do the same thing.

DECLARE @SQL VARCHAR(MAX)
CREATE TABLE #temp1 (DRIVE CHAR(1), MB_FREE INT)
INSERT INTO #temp1 (DRIVE, MB_FREE)
EXEC xp_fixeddrives
CREATE TABLE #TEMP2(SUBDIRECTORY VARCHAR(256),
DEPTH INT, [FILE] INT, FULL_PATH VARCHAR(256), COMMAND VARCHAR(512))
SELECT @SQL=COALESCE(@SQL,”)+ ‘INSERT INTO #TEMP2 (SUBDIRECTORY, DEPTH, [FILE]) EXEC Master.dbo.xp_DirTree “‘ + DRIVE +
‘:\MSSQL\DATA”, 1, 1; UPDATE #TEMP2 SET FULL_PATH = ”’ + DRIVE + ”’ + ”:\MSSQL\DATA\” + SUBDIRECTORY WHERE FULL_PATH IS NULL; ‘
FROM #temp1
PRINT @SQL
EXEC (@SQL)
DELETE
FROM #TEMP2
WHERE [FILE] = 0
OR SUBDIRECTORY LIKE ‘%CER’
OR SUBDIRECTORY LIKE ‘MSSQLSYSTEMRESOURCE%’
OR SUBDIRECTORY LIKE ‘distmdl%’
OR FULL_PATH IN
(
SELECT PHYSICAL_NAME
FROM SYS.MASTER_FILES
)
SELECT FULL_PATH, ‘DEL ‘ + FULL_PATH AS COMMAND
FROM #TEMP2
DROP TABLE #temp1
DROP TABLE #TEMP2

Posted in Uncategorized | Print | 1 Comment »