This is my submission for T-SQL Tuesday on Files and filegroups.  You can find the blog .

 

One of the things I’m known for in the shops I work in is giving my DBAs some interesting exercises.  And sometimes they even come with prizes.  This is an exercise I’ve been giving off and on for a few years now.  I typically point them to a test server and send them to the MyDocs folder under one of the profiles.  In there is a list of files that I want them to delete.  Typically the email has similar verbiage to this:

 

I have an assignment for you guys.  I want you to go to the following location and delete this list of files.

And whoever gets to that server first and deletes those files first gets their pick of these 3 books.

 

They always rush to be the first there and I can hear their initial failure one at a time as they find out it’s not just an exercise in speed.  Just for fun, here’s a screenshot of the files I have them delete.

 

About that time I follow-up with another email.  It says:

You’ve just discovered that the files cannot be easily deleted.  The only hint I’ll give you is that I did it by highlighting a feature in the new version of SQL Server. 

Good luck.

 

For a few years now, the race has been whoever could find the obscure setting in filestream the fastest (because they figure that’s the best place to go).  There has to be something in filestream that’s doing it.  So they dig through everything filestream-related they can find.  They dig and they dig and they dig.  They put together some test DBs and do their best to recreate the issue.  I hear all kinds of wacky theories flying around.  But they never hit that magic bullet that makes it all come together (if you don’t mind me mixing metaphors).

It typically takes them 2-3 days before they give up for good.  I’ll tell you something… in the years I’ve been doing this I’ve never had anyone actually get it with no prompting.  So then at the end, we come together and talk about the process they went through to troubleshoot this issue and their reasoning.  They talk about handles and locked files and permissions, and all the new features in SQL Server that would cause something like that, but they’re just not sure because they couldn’t find anything on google and they don’t know the undocumented XPs, etc. 

And as it turns out, this exercise has nothing to do with the files at all.  I mean, it does, but it’s really meant to serve a much greater purpose.  I want to teach them that the customer always lies, or does his best to throw you off track by sprinkling whatever piece of misinformation he has into his request.  And you never know what they’ll do to their systems.  I want to teach them the following things:

1. Listen to the wording.
2. Users lie.
3. Think for yourself, don’t get distracted by what they tell you.
4. Ask Questions… for the love of GOD ask questions.

 

So what’s the resolution?  Tell ya what, I’ll give you the code to repro it and that’ll tell you what the issue is.

create database UserDB

on

(

name=UserDB1,

filename=’c:\users\sean.midnight\my documents\Master20120105.bak’

),

 

(

name=UserDB2,

filename=’ c:\users\sean.midnight\my documents\BlogPost.docx’

),

(

name=UserDB3,

filename=’ c:\users\sean.midnight\my documents\Expenses.xlsx’

)

logon

(

name=UserDBLog,

filename=’ c:\users\sean.midnight\my documents\LovePets.pdf’

)

 

It’s not always exactly like that, but this is one variation of it.  Now, you may wanna say that I lied, but didn’t.  What I said was that this hinged on a feature in the new version of SQL Server.  But I didn’t say it was a new feature.  It is in fact a feature in the new SQL Server, it just so happens to be a feature of all of them (well, most of them).  And that feature is the ability to create DB files of any name and any extension you like.

Happy T-SQL Tuesday, and I hope this fits in with the theme.