Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I find and clean up unused (i.e. not used by any attached database) SQL Server database files (.mdf, .ldf, .ndf) that take up extra space on my server?

share|improve this question
1  
I blogged about this here, presenting a T-SQL solution that leverages xp_cmdshell. (There are many ways to do this.) –  Jon Seigel Sep 22 '13 at 2:35
    
@JonSeigel The solution I have posted below will find all database files in all directories on all drives attached to the SQL server. It also emails you only when there are things to be addressed. Your solution is definitely the start of something similar in TSQL but I don't believe it would be fair to directly compare them at this point, unless you want to expand on your solution, which would be awesome as having the option to do this with TSQL would be something I am sure others might want. –  Chris Magnuson Sep 23 '13 at 16:15
add comment

1 Answer

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 "\\\\","\" <#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 you want to exclude them from this check just add another Where fullname –notlike “<Path where you have files you don’t want checked>” | 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 <YourSQLServerNameGoesHere>

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.

share|improve this answer
    
I really hope the server on which you've set up a job to run this is a development server... seems pretty suspect there are always files hanging around not being used. –  Jon Seigel Sep 22 '13 at 2:38
    
@JonSeigel I use this in production. It does not delete any files but simply alerts me to their presence so that I can either exclude them because they are legitimate or clean them up if I don't want them. Either way it gives me the information I need to make the decision instead of it just being forgotten about. –  Chris Magnuson Sep 23 '13 at 15:54
    
Alerting vs. deleting wasn't my point. Who or what is creating these situations in the first place? This is highly suspect. You shouldn't need to be running something like this every week. Granted, it might be good to run it occasionally, but if it's finding stuff every week, I would be trying to solve that problem. –  Jon Seigel Sep 23 '13 at 17:29
    
@JonSeigel Gocha didn't understand what you meant. I think we may differ in philosophy of how these things should be handled. My take is that when I fix or clean up something it is not really fixed until the system itself will ensure that it stays in the fixed state indefinitely or alerts me when it is no longer fixed. I may not get this email for a year or more but I want to have it check every week because I want this completely out of my head and I want to know that if it ever happens again my system will tell me (push) and I don't have to check (pull). –  Chris Magnuson Sep 25 '13 at 12:34
    
That makes sense. I was concerned it was actually finding stuff regularly. We're on the same page, I think. :) –  Jon Seigel Sep 25 '13 at 13:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.