Removing offline databases and orphaned data files from your SQL Server

What are orphaned data files?

Orphaned database files are files not associated with any attached database (live database).

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

If you manage a lot of development and test environments, this can certainly occur.

Usually, when you take a database offline and forget to put it back online before remove.

By the way, this check is a part of our SQL Server Health Check service.

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.

  1. SELECT
  2. 'DB_NAME' = db.name,
  3. 'FILE_NAME' = mf.name,
  4. 'FILE_TYPE' = mf.type_desc,
  5. 'FILE_PATH' = mf.physical_name
  6. FROM sys.databases db
  7. INNER JOIN sys.master_files mf ON db.database_id = mf.database_id
  8. WHERE db.state = 6

Orphaned database files

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

  1. DECLARE @DefaultDataPath VARCHAR(512), @DefaultLogPath VARCHAR(512); 
  2.  
  3. SET @DefaultDataPath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(512));
  4. SET @DefaultLogPath = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS VARCHAR(512));
  5.  
  6. IF OBJECT_ID('tempdb..#OrphanedDataFiles') IS NOT NULL
  7. DROP TABLE #OrphanedDataFiles;
  8.  
  9. CREATE TABLE #OrphanedDataFiles (
  10. Id INT IDENTITY(1,1),
  11. [FileName] NVARCHAR(512),
  12. Depth smallint,
  13. FileFlag bit,
  14. Directory VARCHAR(512) NULL,
  15. FullFilePath VARCHAR(512) NULL);
  16.  
  17. INSERT INTO #OrphanedDataFiles ([FileName], Depth, FileFlag)
  18. EXEC MASTER..xp_dirtree @DefaultDataPath, 1, 1;
  19.  
  20. UPDATE #OrphanedDataFiles
  21. SET Directory = @DefaultDataPath, FullFilePath = @DefaultDataPath + [FileName]
  22. WHERE Directory IS NULL;
  23.  
  24. INSERT INTO #OrphanedDataFiles ([FileName], Depth, FileFlag)
  25. EXEC MASTER..xp_dirtree @DefaultLogPath, 1, 1;
  26.  
  27. UPDATE #OrphanedDataFiles
  28.  
  29. SET Directory = @DefaultLogPath, FullFilePath = @DefaultLogPath + [FileName]
  30. WHERE Directory IS NULL;
  31.  
  32. SELECT
  33. f.[FileName],
  34. f.Directory,
  35. f.FullFilePath
  36. FROM #OrphanedDataFiles f
  37. LEFT JOIN sys.master_files mf ON f.FullFilePath = REPLACE(mf.physical_name,'\\', '\')
  38. WHERE mf.physical_name IS NULL AND f.FileFlag = 1
  39. ORDER BY f.[FileName], f.Directory
  40.  
  41. DROP TABLE #OrphanedDataFiles;

Also, you can do this using dba tools (PowerShell).

How to fix it?

Since they are still offline, they probably not necessary.

  1. Consider removing the files.
  2. If there is a potential, you may need something from them, backup first.

More information

Multi-server Script to Find Orphaned Data Files using PowerShell, Prashanth Jayaram- SQLShack.
Microsoft – ALTER DATABASE SET options.

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *