Searching for orphaned DB files on the SQL server

,

Sometime it happens on SQL server we can find MDF, LDF or NDF files which belong to no currently used DB. They are just consuming disc space. If there are more administrators and SQL server is quite big (more discs, lot of DBs) it could easily  happen somebody's detached DB from the server and forgot to move / delete / archive its files.

But how could we know which files are still used and which of them are orphaned?

As a solution I've prepared PowerShell script which:

  1. connects to the SQL server and gather list of database files (the full path),
  2. goes trough the whole server (except drives which are excluded),
  3. compares DB files found on the file system to list of database files from SQL server and the files not found in the list are reported as files which could be deleted from the SQL server point of view.

I recommend to exclude "C" drive from the search because of many files / directories needed to be checked. You can also exclude e.g. drive dedicated for backups.

##############################################################################################################
# Author: Martin Tydor
# Create date: 17/09/2020
# Description: PowerShell script for finding unnecessary DB files across the server drives.
# The result is the list of commands for removing the files which should be checked and
# executed in new PowerShell window.
# 
# Prerequisities:
# SQL server permissions for the listing DB files and Read/Delete permission on Filesystem is required.
#
#                          !!!Warning!!!
#
# List can contain DB files stored on the server by some purpose and should be checked before execution!
# Files you want to leave on the server should be removed from the list!
# The script will not work on SQL server with more than one SQL server instance!
# Execute script on your own risk and responsibility!
##############################################################################################################

#configuration part
$SQLServerPort = '1433' #SQL server port
[string[]]$Includes = @('*.MDF', '*.NDF', '*.LDF') #Filetypes wich should be found
$Drives ='[C]' #Exclude drives, e.g. [CDMP] - server will not look for files on that drives

#clear screen
cls

#getting data from DB
try {
    #Set datasource
    $dataSource = [System.Net.Dns]::GetHostByName($env:computerName).HostName+','+$SQLServerPort
    Write-Host "SQL server datasource: $dataSource"  -ForegroundColor green
    #Get DB files from SQL server
    $connectionString = “Server=$dataSource;Database=Master;Integrated Security=True;”
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString

    $connection.Open()
    $resultsDataTable = New-Object System.Data.DataTable
    $command = $connection.CreateCommand()
    $query = “Create Table #temp (physical_name nvarchar(260));
        Exec sp_msforeachdb 'Use [?]; Insert Into #temp (physical_name) Select physical_name From sys.database_files';
        Insert Into #temp (physical_name)
        SELECT mf.physical_name FROM sys.databases db INNER JOIN sys.master_files mf ON db.database_id = mf.database_id 
		except 
		select physical_name from #temp;
        Select * From #temp
        drop table #temp”
    $command.CommandText = $query

    $result = $command.ExecuteReader()
    $resultsDataTable.Load($result) 

    $DBFilesArray = @()     
    foreach ($Row in $resultsDataTable.Rows){$DBFilesArray += @(,$($Row[0]).ToString())}

    $result.Dispose()
    $resultsDataTable.Dispose()
    $connection.Close()
 }
catch {
  Write-Host "An error occurred:"
  Write-Host $_
  Break
}

#search DB files on file system
$files =  get-psdrive -PSProvider "FileSystem" | Where-Object { $_.Name -notmatch $Drives} `
| % {get-childitem $_.Root -include $Includes –force -Recurse -ErrorAction SilentlyContinue | Where-Object { $_ -notin $DBFilesArray } } `
| Sort-Object -Property FullName 

#write count of all found files
Write-Host "Total unused DB files: $($files.Count)" -ForegroundColor green
Write-Host "Copy rows with files you want to remove and execute them in new PowerShell window" -ForegroundColor yellow
Write-Host "*******************************************************************************************"

#write found files
foreach ($file in $files) {
    Write-host "Remove-Item `"$file`"  " -nonewline 
    Write-host  "#$($file.Length)  bytes" -foreground yellow  
    Write-host  "#Updated:$($file.LastWriteTime)" -foreground yellow 
}

Rate

Share

Share

Rate