Removing Databases and Orphaned Datafiles from your SQL Server
What are Orphaned Datafiles?
Orphaned database files are files that are 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 large number of development and test environments, this can certainly happen.
Usually, when you take a database offline and forget to bring it back online before removing it.
Why should you care about them?
Offline databases and Orphaned database may be using unnecessary space on your SQL Server storage.
How can we control them?
Offline databases
Run the following script to list all offline databases in your installation.
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 following script and find an example of an Orphaned database.
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 dba tools (PowerShell).
How to fix them?
Since they are still offline, they are probably not needed.
- Consider removing the files.
- If there is a potential you might need something from them, back them up first.
