Find all the unused SQL database files on your SQL server

If your like me you have been frustrated by finding stray mdf, ldf, or ndf files on your SQL server from databases deleted long ago that should have been cleaned up but someone forgot.

To permanently solve this problem I have written a couple of PowerShell scripts and configured a scheduled task to send an email to our DBA group if we ever have SQL database files (mdf, ldf, ndf) sitting on any local drive attached to an SQL server that are not currently being used by a database on that SQL server.

First up we a simple helper function that allows us to run SQL statements from anywhere .net is installed without SQL specific utilities installed:

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
    
    $connection.Close()
    $dataSet.Tables
}

Next up we have have Get-SQLDatabaseFilesNotUsed:

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 "\\\\","\" <#SQL server allows \\ in paths and just uses it like \#> }}

    $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 exclude from this check just add another “Where fullname –notlike “<Path where you have files you don’t want checked>” |” to the others you see that filter out some files that share the same extensions as SQL database files even though they are not databases.

Lastly we have a script file Test-SQLDatabaseFilesNotUsed.ps1 that will want to call from your scheduled task:

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

$OutputMessage = ""

$FromAddress = "scheduledtasks@tervis.com"
$ToAddress = "WindowsServerApplicationsAdministrator@tervis.com"
$Subject = "SQL Server has data or log files that are not being used"
$SMTPServer = "cudaspam.tervis.com"

$OutputMessage = Get-SQLDatabaseFilesNotUsed -SQLServerName sql

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

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 a +100GB already and from this point forward you won’t have to worry if you have space being used up but the database files that time forgot.

Find all files with NTFS Alternate Data Streams using PowerShell

1. Open Powershell
2. cd to the directory or the root of a drive where you want to recursively look for alternate data streams
3. Run the following command (requires PowerShell V3)

gci -recurse | % { gi $_.FullName -stream * } | where stream -ne ':$Data'

To test that this works properly navigate to a random directory on your computer and run the following commands:

new-item test.txt -Type file
Add-Content test.txt "test content"
Add-Content test.txt -str HiddenStream "Hidden test content stored in the stream"
get-content .\test.txt
get-content test.txt -str HiddenStream

Now navigate to some folder above the directory where you ran these commands and run the command to find all files with alternate data streams and you should see our test.txt file come up.

For a more complete description of what you can do with NTFS Alternate Data Streams in PowerShell V3 please check out this blog post.

Dealing with multiple parameters passed to exe in powerhsell

If you have ever needed to call an exe application from PowerShell and needed to pass multiple parameters to the exe then the technique I outline here will really help you out.

Declare a variable that holds the executable name as a string and any other variables you might want to use

$pfm = “pfm”

$MountPath = “T:”

As a side note the application I am calling is Pismo File Mount which is by far the best free (even for commercial use) software I have ever used for mounting ISOs

Declare all your parameters as an object array

$Mount = “mount”,”-m”,”$MountPath”

Now in your code you can do the following

& $pfm $Mount “$MSDNIsoPath\en_visual_studio_2010_premium_rc_x86_dvd_489527.iso”

If I wanted to write out all my parameters every time I could always write in PowerShell:

Pfm mount -m “$MountPath” “$MSDNIsoPath\en_visual_studio_2010_premium_rc_x86_dvd_489527.iso”

I can’t say as I have figured out why this works this way and if anyone has an explanation I would love to hear it in the comments.

How to rebase differencing disk VHDs (or how to change the parent of a differencing VHD when the parent has moved)

I am a big fan of differencing VHDs as the provide tremendous power when working with large database but they also come with their own set of complications.

One of those complications that can arise is when due to files being moved or folder structures being reorganized the parent VHD of your differencing VHD is no longer accessible. After you try and attach your differencing VHD you may see an error message like this one:

We can use the following power shell from a Windows 7 PC with the latest version of Microsoft Virtual PC that comes with Windows XP Mode installed to take a look at the parent property of our differencing disk to see if something is wrong

$vpc=new-object -com VirtualPC.Application
$VHDChild = $vpc.GetHardDisk("C:\NAV2010.06.10_Diff")
$VHDChild
$VHDChild.Parent

In my case I recently changed the IP address of a NAS that was storing the parent VHD and assigned a DNS record to the NAS so that I wouldn’t have to keep track of it’s IP address but now the path to the parent VHD that is stored in my differencing VHD is no longer valid.

Thankfully this is fairly easy to fix. All we need to do is add the following code so that we get a new $VHDParent object using the corrected path and then assign that object to the Parent property of our differencing disk

$VHDParent = $vpc.GetHardDisk("\\devnas\Data_Backups SQLCluster1\NAV2010.06.10.vhd")
$VHDChild.Parent = $VHDParent
$VHDChild.Parent

Now we can attach our differencing VHD without error and get back to work =).