How can I find and clean up unused database files?
There are two PowerShell functions and a PowerShell script will help automate the process of finding unused SQL database files and alerting you to their presence so that you can clean them up.

First Invoke-SQL function:

Save the code for the Invoke-SQL function that I have posted as an answer to "How do you run a SQL Server query from PowerShell?" into a file named Invoke-SQL.ps1.

Second Get-SQLDatabaseFilesNotUsed function:

Save the following function into a file named Get-SQLDatabaseFilesNotUsed.ps1.

function Get-SQLDatabaseFilesNotUsed {
    param ( 
        [parameter(Mandatory = $true)][string]$SQLServerName,
        [switch]$IncludeEqual
    )

        $SQLCommand = @"
set nocount on
SELECT DB_NAME([database_id])AS [Database Name], 
        [file_id], name, physical_name, type_desc, state_desc, 
        CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
"@

    $Results = Invoke-Sql -datasource $SQLServerName -database master -SQLCommand $SQLCommand 
    $SQLDatabaseFilesInUse = $Results | select @{Name="fullname";Expression={$_.physical_Name -replace "\\\\","\" &lt;#SQL server allows \\ in paths and just uses it like \#&gt; }}

    $SQLDatabaseAndLogFilesOnDisk = invoke-command -ComputerName $SQLServerName -ScriptBlock { 
        $Filesystems = get-psdrive -PSProvider FileSystem
        Foreach ($FileSystem in $Filesystems) {
            Get-ChildItem $FileSystem.Root -Recurse -include *.mdf,*.ldf,*.ndf -File -ErrorAction SilentlyContinue | select fullname 
        }
    }

    $SQLDatabaseAndLogFilesOnDisk = $SQLDatabaseAndLogFilesOnDisk | 
    Where fullname -notlike "C:\windows\winsxs*" | 
    Where fullname -notlike "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\*" |
    select fullname


    Compare-Object -ReferenceObject $SQLDatabaseFilesInUse -DifferenceObject $SQLDatabaseAndLogFilesOnDisk -Property FullName -IncludeEqual:$IncludeEqual | FT -AutoSize

}


One thing to note, if you have additional database files that you want to keep on disk and you want to exclude them from this check just add another Where fullname –notlike “&lt;Path where you have files you don’t want checked&gt;” | to the others you see above.

Third Test-SQLDatabaseFilesNotUsed script:

Save the following code into a Test-SQLDatabaseFilesNotUsed.ps1 script file.

. c:\scripts\Invoke-SQL.ps1
. c:\scripts\Get-SQLDatabaseFilesNotUsed.ps1

$OutputMessage = ""

$FromAddress = "scheduledtasks@YourDomain.com"
$ToAddress = "DBA@YourDomain.com"
$Subject = "SQL Server has data or log files that are not being used"
$SMTPServer = "MailServer.YourDomain.com"

$OutputMessage = Get-SQLDatabaseFilesNotUsed -SQLServerName &lt;YourSQLServerNameGoesHere&gt;

if ($OutputMessage)
{
    Send-MailMessage -From $FromAddress -to $ToAddress -subject $Subject -SmtpServer $SMTPServer -Body ($OutputMessage | FT -autosize | out-string -Width 200) 
}


Be sure to substitute the name of your SQL server for  in the code above.

Now save all of these in a C:\scripts directory and create a scheduled task with whatever schedule you like (I have ours run once per week) that executes:

Powershell.exe -noprofile -file c:\scripts\Test-SQLDatabaseFilesNotUsed.ps1


This has saved me +100GB already and from this point forward you won’t have to worry about having space used up by database files from databases removed from SQL server long ago.