Home » Powershell »sql server »sqlserverpedia-syndication »SSC »Tech and Learning » Currently Reading:

Use Powershell to find and punish – er, delete – rogue files

I like to know what’s on the drives that are supposed to be dedicated to data and log files. So I run a quick check of sys.master_files:

SELECT LEFT(physical_name, 1) as Drive,
SUM(size)*8/1024 as SizeMb
FROM sys.master_files
GROUP BY LEFT(physical_name, 1)

And then I compare the numbers I got back against what Windows says. Hmm, odd. SQL says it’s using up 296,869 Mb (that’s ~289 Gb) of space on U:\, but when I go to Start > Computer it says it’s using up 390Gb of space on U:\.

Rogue files! Rogue files on my data drive! I now have to HUNT DOWN AND PUNISH those 101 Gb of files freeloading on my data drive. And oh, how they’ll pay….

First, Gather Data and Compare

Step 1: Get the physical_name of all files on that drive from sys.master_files. That’s easy: SELECT physical_name FROM sys.master_files WHERE physical_name like ‘U%';

Step 2: Open powershell, navigat to U:\, and run get-childitem -name -recurse -filter *.*

Step 3: Compare the lists. You can, of course, import to SQL and compare that way, or just paste each list into Excel for a look-see.

Sure enough, there were about 100Gb of duplicate data files – MDFs, NDFs, and LDFs, if you can believe it! – on that server that hadn’t been touched in years. Time for vengance…

Delete Files, if you Dare

There are a number of ways we could go about removing these files, of course. In my case, it so happens that all my current, supposed-to-be-there files have recent modification dates, and everything else is pre-2012. That makes it very easy!

Let’s start with a test script, that will just list the things we want to delete:

get-childitem “U:\SQL03\” | where {$_.lastwritetime -lt (get-date).adddays(-365) } |% {write-output $_.fullname}


  • get-childitem “U:\SQL03\” |    Get all objects in the specified directory, and pipe them to the next operation.
  • where {$_.lastwritetime -lt (get-date).adddays(-365) } |  Test each item’s “last write time” date against today -365 days; if they’re earlier than that date, pipe them to the next operation.
  • % {write-output $_.fullname}   Take each item and output its name.

Awesome. All we have to do to change this into a dangerous, file-destroying script*is to replace write-output with remove-item.

Wait, I could use this to routinely clean up old backups too, right?Schedule powershell as an Agent job

Funny you should ask! Yes.

Let’s say you want to delete backup files in a dedicated folder that are older than N days, and repeat regularly. Just take this and schedule it in a SQL Agent job! (You might want to change the -365 to -30, or whatever your given retention period is, in days.)

Yeah, but my SQL backups go to a UNC share :(

Except that your backups aren’t on a local drive; they’re on a UNC share, and you’re getting a weird error. No problemo!

Your new, file-destroying script* that will indeed delete all files older than 30 days now looks like this:

cd “\\Server01\SQLShare”; get-childitem “\\Server01\SQLShare \SQL03\FULL” | where {$_.lastwritetime -lt (get-date).adddays(-30) } |% {remove-item $_.fullname}

Let’s make this slightly safer, and only delete the *.bak files older than 30 days:

cd “\\Server01\SQLShare”; get-childitem “\\Server01\SQLShare \SQL03\FULL” -filter *.bak | where {$_.lastwritetime -lt (get-date).adddays(-30) } |% {remove-item $_.fullname}

 There we go, wielding Powershell like Foe-hammer in a goblin cave**. Feels good, don’t it?

Happy days,
Jen McCown

*Note that the sys.master_files “size” column isn’t in Mb, but in pages.  To translate pages to Mb, SELECT name, (size*8)/1024 as SizeInMb from sys.master_files.
*Which you will test thorougly before you use in a production environment, So Help You Codd.
** Too nerdy?

Inspirational code source: Powershell delete files in subfolder older than 30 days with out deleting subfolders

Comment on this Article:

Release Date: Minion by MidnightDBA

Just 1 day 4 hours 22 minutes 39 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/