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.
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:
- Listen to the wording.
- Users lie.
- Think for yourself, don’t get distracted by what they tell you.
- 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
filename=’ c:\users\sean.midnight\my documents\BlogPost.docx’
filename=’ c:\users\sean.midnight\my documents\Expenses.xlsx’
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.
Incoming search terms:
- dba exercises sql
- dba exercises
- sql dba exercises
- tsql delete file
The best database career advice you’ve never heard!
Become a DBA. Become a BETTER DBA. Use the Roadmap.
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.)
Visit www.DBARoadmap.com for info, forums, and more!
TagsArchitecture blunders Camtasia Career Coding Standards CPU data generator DBA Development disaster firewall Idiots Inside SQL Server Interview ITBookworm Jobs Kalen Delaney Katmai Ken Henderson LiteSpeed MidnightDBA PASS Summit 2011 Pinnacle politics Powershell Powershell Hero Query techniques Red-Gate reporting restore SQL Express 1433 SCM SQL Server SQLServerDVD.com SSIS SSRS Training troubleshooting TSQL Tuesday tuning Tutorials Video post videos Yukon
Interview: Kalen Delaney!
- Reading: Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions by Itzik Ben-Gan
- Repost: “Problems with my new book” by Grant Fritchey
- Announcing SQL Server 2012 Query Performance Tuning by Grant Fritchey
- Quick Review: Logitech Trackball
- Book Review: Microsoft SQL Server 2008 Internals, by Kalen DeLaney et al