SQL Server Health Check

Removing Offline Databases and Orphaned Data Files From Your SQL Server

Updated April 8, 20263 min read

Orphaned data files: Ploom finding a cobwebbed file box left on a doorstep

What are orphaned database data files?

Orphaned database files are not associated with any live database.

Sometimes when you drop a database from a SQL Server instance, the underlying files are not removed.

This can certainly occur if you manage many development and test environments.

Orphaned files usually appear when a database is taken offline and not put back online before it is removed.

Why should you care about them?

Offline databases and orphaned files may be using unnecessary space in your SQL Server storage.

How can I check them?

Offline databases

Run the script below to list all offline databases from your instance.

T-SQL
SELECT 'DB_NAME' = db.name
	,'FILE_NAME' = mf.name
	,'FILE_TYPE' = mf.type_desc
	,'FILE_PATH' = mf.physical_name
FROM sys.databases db
INNER JOIN sys.master_files mf ON db.database_id = mf.database_id
WHERE db.STATE = 6

Orphaned database files

You can run the script below and find the orphaned database from an instance.

T-SQL

DECLARE @DefaultDataPath VARCHAR(512)
	,@DefaultLogPath VARCHAR(512);

SET @DefaultDataPath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(512));
SET @DefaultLogPath = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS VARCHAR(512));

IF OBJECT_ID('tempdb..#OrphanedDataFiles') IS NOT NULL
	DROP TABLE #OrphanedDataFiles;

CREATE TABLE #OrphanedDataFiles (
	Id INT IDENTITY(1, 1)
	,[FileName] NVARCHAR(512)
	,Depth SMALLINT
	,FileFlag BIT
	,Directory VARCHAR(512) NULL
	,FullFilePath VARCHAR(512) NULL
	);

INSERT INTO #OrphanedDataFiles (
	[FileName]
	,Depth
	,FileFlag
	)
EXEC MASTER..xp_dirtree @DefaultDataPath
	,1
	,1;

UPDATE #OrphanedDataFiles
SET Directory = @DefaultDataPath
	,FullFilePath = @DefaultDataPath + [FileName]
WHERE Directory IS NULL;

INSERT INTO #OrphanedDataFiles (
	[FileName]
	,Depth
	,FileFlag
	)
EXEC MASTER..xp_dirtree @DefaultLogPath
	,1
	,1;

UPDATE #OrphanedDataFiles
SET Directory = @DefaultLogPath
	,FullFilePath = @DefaultLogPath + [FileName]
WHERE Directory IS NULL;

SELECT f.[FileName]
	,f.Directory
	,f.FullFilePath
FROM #OrphanedDataFiles 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 #OrphanedDataFiles;

Also, this task can be accomplished by using dbatools.io (PowerShell).

How to fix it?

Since they are still offline, they are probably not necessary.

Therefore:

  1. Consider removing the files.
  2. If there is a potential need for something from them, ensure to back up first.

More information:

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜