A PowerShell Script to Help You in Cleaning Up Old or Unused Files in Your SQL Data File Directories   Leave a comment

Here is a PowerShell script to list non-locked files in SQL Data directories as possible candidates to delete, in order to free up space. This can be useful for identifying old files that, for whatever reason, were not deleted and may be wasting disk space.

Note: before deleting any file, make sure it is not needed. For example, if a database is offline, its data files will be closed (not locked) and thus show up on this report, but you may not want to delete the files in case someone should need to bring the database back online again.

There are two modifiable sections in the script that allow you to prevent files from being evaluated based on either the file extension or the file name.

The script works by simply looping through files in the directory that you pass in as a parameter, and attempts to open the first line of each file. If a file is locked (as in a live SQL data or transaction log file), the attempt to open it and read the first line will fail, and the file will not be reported on. If the attempt to open and read the first line is successful, then the file will be reported on as a candidate for deletion.

Being the paranoid, DBA-type, I have written the script so that it will not perform the actual deletion of any files. Again I recommend that a human being look at each listed file and make the final decision of whether to delete.

I like to run the script from a PowerShell 2.0 ISE window. The example execution in the script assumes you save this script to your local system as “C:\PowerShell Scripts\UnusedFiles\UnusedFiles.PS1.” Please follow the directions within the comments of the script for a sample execution.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

param([string]$theDir="")

#Norm Enger - 4/30/2011 - PowerShell script to list non-locked files in SQL Data directories
#as possible candidates to delete, in order to free up space. Note: before deleting any
#file, make sure it is not needed. For example, if a database is offline, its data file
#will be closed (not locked) and thus show up on this report, but you would not want to delete
#the file in case someone should need to bring the database back online again.

#To run, open a Powershell window and run a command like the following (starting with
#the amperstand "&" character)...
#& "C:\PowerShell Scripts\UnusedFiles\UnusedFiles.PS1" -theDir "C:\mySQLDirectory\Data"

#Output will be similar to the following:
#C:\mySQLDirectory\Data\New Text Document.txt (10/8/2010 4:46:33 PM)

#Array of extensions to exclude from report (change as needed)...
$arrExcludeExtensions = ".preg",".old",".cer"

#Array of file names to exclude from report (change as needed)...
$arrExcludeFileNames = "distmdl.mdf","distmdl.ldf", `
	"mssqlsystemresource.mdf","mssqlsystemresource.ldf"

#Sets the folder location
Set-Location $theDir

foreach($file in get-childitem | Sort-Object $_.Name `
	| Where-Object {$arrExcludeExtensions -notcontains $_.Extension `
	-and $arrExcludeFileNames -notcontains $_.Name})
{
#Get-Content returns error if the file is in use/locked
#We limit lines to try to read at 1 using TotalCount...
$content = Get-Content $file -TotalCount 1 -ErrorAction silentlycontinue
	if($?){
		$file.FullName.ToString() + `
		" (" + $file.LastWriteTime.ToString() + ")"
	}
	else{} #write out nothing
}

Posted April 30, 2011 by Norm Enger in Microsoft SQL Server, Powershell

About these ads

Posted April 30, 2011 by Norm Enger in Microsoft SQL Server, Powershell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: