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)
ORDER BY Drive;

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}

Explanation:

• 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?

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
http://www.MidnightDBA.com/Jen

*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

Invitation to T-SQL Tuesday #040: File and Filegroup Wisdom

Hello and welcome to the third T-SQL Tuesday of 2013!

Over three years ago, Adam Machanic (Twitter) dreamed up the idea of T-SQL Tuesday:

Each month a blog will host the party, and about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic. The event is called “T-SQL Tuesday”, but any post that is related to both SQL Server and the theme is fair game. So feel free to post about SSIS, SSRS, Java integration, or whatever other technologies you’re working with in conjunction with SQL Server. Even if your post includes no T-SQL we still want to see it.

A lot of you know that I’m a big fan of the basics, especially those basics that are grossly misunderstood or overlooked. So for this month’s T-SQL Tuesday, we’re talking about

Filegroups!

Hold on! I already hear some of you shouting, “Boooooring!!!”  You don’t have to write a file and filegroup primer, if you don’t want to. Get creative, get tangential! Some of my suggestions:

• Maybe you’d like to talk about partitioning indexes or tables across filegroups
• Or performance benefits (still!) of assigning tables to specific drives, via filegroups
• Or FILESTREAM, specifically (hey, that’s something that requires a specialized filegroup!)
• Or some horrible misuse of filegroups you’ve seen once  (we always love a SQL horror story)
• Or, if you have a mind, a file and filegroup backup/restore primer! (Why not? I’m not the only one who loves basics.)

How to Play

1. Window of opportunity: Your post must go live between 00:00:00 GMT on Tuesday the 12th of March and 00:00:00 GMT on Wednesday the 13th.
2. Show some love: Your blog post has to link back to the hosting blog, and the link must be anchored from the logo (the blue one shown here, not the stick figure “YAY!” one) which must also appear at the top of the post.
3. Check your work: If trackbacks won’t work properly then please leave a comment below so that your post is accounted for in the roundup.

Bonus points!

Bonus points are like brownie points – there’s no way to redeem them, but you feel good about it anyway. Earn some by using these brilliant suggestions:

1. Advertise! Include a reference to T-SQL Tuesday in the title of your post.
2. Tweet! Use the hash tag #TSQL2sDay to follow links and other relevant conversations.
3. Host! Consider hosting T-SQL Tuesday yourself. If you’re interested let Adam Machanic Know. If you’ve participated in two T-SQL Tuesdays previously and you keep up your blog (blog monthly for the last six months) then he’ll put you in the rotation.

FileTable: SQL Server 2012′s little gasp-maker

When was the last time you looked at a SQL Server new feature list and said, “Ohhh, WOW!”?  Was it around March of last year? No? Then you might have missed something really, really cool.

Let me introduce you to my friend, SQL Server 2012′s FileTable.

A Brief History of Files

Before SQL Server 2008, if you wanted to store file information (“unstructured data”) in your database, you had two options: you could either store your file in the database as an IMAGE (or VARBINARY) column, which was good for management, but pretty damn bad for performance. Or, you could store the file in the file system, and store a link in a database…great for performance, lousy for managing your files and keeping them in sync with the database.

Microsoft addressed this in SQL Server 2008 with FILESTREAM, which lets you store files in the NT file system, and keep them transactionally consistent with the database. What’s more, you can search those files’ contents using Full-text search. Good stuff, that.

FileTable Defined

Now in SQL Server 2012, Microsoft has built FileTable on the FILESTREAM foundation.  FileTables are a special kind of table that stores files in the database – where they can be managed, accessed, backed up, and searched (via Full-text or the new and similarly awesome semantic search). AND! AND! AND! Those files can be accessed from Windows as if they were stored on the NT file system.  SQL Server exposes a “virtual file share”, which you can open up just like any UNC and look at, add, remove, and modify files all the day long.

You can access FileTable files just like any other file stored on the server: programatically, or via a directory window!

That’s really spectacularly cool, but you’ve got to see it in action.

Get Set Up for FileTable

Before you can use FileTable on an instance, get your prerequisites in order! Note: Standard warnings about testing this in a test environment apply. If you run out and do this on production without trying it out elsewhere first, it’s your own durn fault, kids.

Enable and configure Filestream:

1. Open SQL Server Configuration Manager.
2. Bring up Properties for your instance.
3. Select Enable Filestream for T-SQL Access AND for File I/O Access
4. And run this statement in SSMS:
   EXEC sp_configure filestream_access_level, 2
   RECONFIGURE
5. Restart the SQL Server service.

Easy-easy. Now, for our example, we’ll create a database with Filestream:

CREATE DATABASE FileTableDemo ON PRIMARY
( NAME = N'FileTableDemo',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemo.mdf'),
 FILEGROUP FilestreamFG CONTAINS FILESTREAM
( NAME = FilestreamData,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemoFS' )
 LOG ON 
( NAME = N'FileTableDemoLog',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemoLog.ldf')
WITH FILESTREAM
(
 NON_TRANSACTED_ACCESS = FULL,
 DIRECTORY_NAME = N'FileTable'
);
GO

As of this moment, your virtual directory is available, at \\{servername}\MSSQLSERVER\FileTable\. BUT you can’t do anything with it yet! You need an actual FileTable, to put your files into.

FileTable in Action

Now let’s create a FileTable:

CREATE TABLE JensAwesomeFiles AS FileTable
WITH
( FileTable_Directory = 'JenAwesomeFiles', 
 FileTable_Collate_Filename = database_default
);

Very simple, very easy. You don’t list any column names or attributes, because a FileTable has a fixed structure that SQL Server already knows. You can now SELECT * FROM JensAwesomeFiles (though it is currently empty), and access it at \\{servername}\MSSQLSERVER\FileTable\JenAwesomeFiles\.

Now, drop a file in that directory. Go ahead, any old file. Create a new text file and save it there, or copy a JPG in. Then when you select from JensAwesomeFiles in SSMS, it shows up:

Go ahead, copy a bunch of files in and select. It’s fun! Know what’s more fun? Changing file names, extensions, or attributes with T-SQL commands. For example:

UPDATE JensAwesomeFiles SET is_readonly = 1 WHERE file_type = 'docx'; -- Set the readonly attribute!
UPDATE JensAwesomeFiles SET NAME = 'NEW NAME_' + name WHERE creation_time > GETDATE()-1; -- Change file names, based on creation date!
DELETE JensAwesomeFiles WHERE is_readonly = 0; -- Delete files RIGHT OUTTA THERE!

After each change, go take a look at the virtual directory, just to Ooh and Ahh over the changes.

What’s it For?

Of course, like anything in computers, this feature is for whatever you can think of. I’ve used it to streamline and improve document handling and searching in companies with legacy applications – where we couldn’t alter the actual file-in-file-out process, but we could point the app to a new directory….and so all our files were dropped straight into SQL Server. Aww, yeah…

Oh, and you PowerShell freaks out there: Tell me you couldn’t have TONS of fun by combining POSH with FileTable. Go on, I dare ya.

Drop me a line, kids, with your “oh WOW”s, your questions, your corrections, and how you’ve used (or will use) FileTable.

Happy days,
Jen McCown

http://www.MidnightDBA.com/Jen