Removing Offline Databases and Orphaned Data Files from Your SQL Server
What Is an Orphaned Data File?
Orphaned database files are files that aren’t associated with any attached (live) database.
Sometimes when you drop a database from a SQL Server instance, the underlying files aren’t removed.
If you’re managing a lot of development and test environments, this can definitely happen.
It usually happens when you take a database offline and forget to bring it back online before dropping it.
Why Should You Care About This?
Offline databases and leftover files may be needlessly taking up space on your SQL Server storage.
How Do You Check for Them?
Offline Databases
Run the script below to list every offline database on your setup.
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 = 6Orphaned Database Files
You can run the script below to find leftover database files on an instance.
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;You can also do this using dbatools (PowerShell).
Need expert support for SQL Server?
Our senior database team supports SQL Server performance tuning, health checks, migrations, Remote DBA services and urgent operational issues.
How Do You Fix It?
Since they’re still offline, they’re probably not needed.
- Consider removing the files.
- If there’s a chance you might need something from them, back them up first.
Related Reading
SQL Server Backup Consulting
Does Your Backup Policy Meet Your Data Loss Risk?
Aryasoft builds a backup strategy aligned with your RPO/RTO targets, audits your existing backup policy, and plans recovery testing.
