Voluntary DBA

Sometimes database files get left behind in the file system, maybe due to choosing to restore to a different location, or someone detached a database and forgot about it. Maybe there are rogue files in your SQL Server data folder.

To find out if there are any, here is a solution that uses the extended stored procedure xp_cmdshell to look in an instance’s data and log folders for files that don’t also appear in sys.master_files. This solution does not require that you have direct permission to the database files, but you will need sysadmin privileges in the instance. For safety reasons, the script only lists unattached files, it does not actually delete them. That part is up to you.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

DECLARE @HkeyLocal nvarchar(18) = N'HKEY_LOCAL_MACHINE';
DECLARE @MSSqlServerRegPath nvarchar(31) = N'SOFTWARE\Microsoft\MSSQLServer';
DECLARE @InstanceRegPath sysname = @MSSqlServerRegPath + N'\MSSQLServer';
DECLARE @dataFolder nvarchar(512);
DECLARE @logFolder nvarchar(512);

EXEC master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultData', @dataFolder OUTPUT;
EXEC master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @logFolder OUTPUT;

DECLARE @cmd nvarchar(1024);

DECLARE @files table (physical_name nvarchar(MAX));

SET @cmd = N'dir "' + @dataFolder + N'" /s /b';
INSERT INTO @files(physical_name) EXEC xp_cmdshell @cmd;

SET @cmd = N'dir "' + @logFolder + N'" /s /b';
INSERT INTO @files(physical_name) EXEC xp_cmdshell @cmd;

DELETE FROM @files WHERE (physical_name IS NULL) OR (RIGHT(physical_name, 4) = '.cer');

SELECT
	f.physical_name
	FROM
	(
		SELECT DISTINCT
			physical_name
			FROM @files
	) f
	WHERE
		NOT EXISTS
		(
			SELECT *
				FROM sys.master_files mf
				WHERE mf.physical_name = f.physical_name
		);

GO

EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO