When you drop a database from a SQL Server instance the underlying files are usually removed. This doesn’t happen however if you set the database to be offline first, or if you detach the database rather than dropping it.
The scenario with offline databases is the one that occurs most often in practice. I might ask if a database is no longer in use and whether I can remove it. A common response is that people don’t think it’s in use, but can I take it offline and we’ll see if anyone screams. I’ll often put a note in my calendar to remove it after a few weeks if no-one has complained. When I do come to remove it, hopefully I’ll remember to put it back online before I drop it so the files get removed, but sometimes I might forget, and in an environment where many people have permissions to create and drop databases you can end up with a lot of files left behind for databases that no longer exist – these are what I’m referring to as orphaned files.
Obviously this shouldn’t happen in production environments where change should be carefully controlled, but if you manage a lot of development and test environments this can certainly occur.
So I created a script I can run on an instance to identify any files in its default data and log directories that are not related to any databases on the instance. Here it is:
--Orphaned database files
DECLARE @DefaultDataPath VARCHAR(512);
DECLARE @DefaultLogPath VARCHAR(512);
SET @DefaultDataPath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(512));
SET @DefaultLogPath = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS VARCHAR(512));
IF OBJECT_ID('tempdb..#Files') IS NOT NULL
DROP TABLE #Files;
CREATE TABLE #Files (
Id INT IDENTITY(1,1),
[FileName] NVARCHAR(512),
Depth smallint,
FileFlag bit,
Directory VARCHAR(512) NULL,
FullFilePath VARCHAR(512) NULL);
INSERT INTO #Files ([FileName], Depth, FileFlag)
EXEC MASTER..xp_dirtree @DefaultDataPath, 1, 1;
UPDATE #Files
SET Directory = @DefaultDataPath, FullFilePath = @DefaultDataPath + [FileName]
WHERE Directory IS NULL;
INSERT INTO #Files ([FileName], Depth, FileFlag)
EXEC MASTER..xp_dirtree @DefaultLogPath, 1, 1;
UPDATE #Files
SET Directory = @DefaultLogPath, FullFilePath = @DefaultLogPath + [FileName]
WHERE Directory IS NULL;
SELECT
f.[FileName],
f.Directory,
f.FullFilePath
FROM #Files f
LEFT JOIN sys.master_files mf
ON f.FullFilePath = REPLACE(mf.physical_name,'\', '')
WHERE mf.physical_name IS NULL
AND f.FileFlag = 1
ORDER BY f.[FileName], f.Directory
DROP TABLE #Files;I wouldn’t say that you can just go delete these once you’ve identified them, but at least now you have a list and can investigate further.
By the way, you might notice a nasty join statement in the above query. This is to deal with instances where the default directories have been defined with a double backslash at the end. SQL Server setup allows this and it doesn’t cause any day-to-day problems, but can make this sort of automation challenging. I’ve included it in this query as I’ve encountered a few people having this issue. In general I’d avoid such joins like the plague.
Making things more complicated
One complication can be where you have multiple SQL Server instances on the same server. This isn’t greatly recommended in production, but is common in devtest. Where a database has been migrated from one instance to another, it’s possible that hasn’t been done correctly and the files still exist under the directories for the old instance and you might then see them as orphaned. I previously posted a script to identify such files:
https://matthewmcgiffen.com/2018/04/24/database-files-down-the-wrong-path/
Also in the case of multiple instances, you might want to report across all of them at once. For that you could use SQL CMS. I’ve posted on that too:
Combining these three techniques makes it relatively easy to identify files that are probably no longer needed. You can get a list of all files that don’t belong to databases on the instances they live under, correlate that to any files that are down the wrong path for any of your instances, then look at what’s left over.