Oct 172018

Well, maybe the title is a little bit harsh. You absolutely can drop a SQL Server database that’s offline, but remember that its files won’t be deleted if you do.

Safely Dropping Databases

At a previous employer, we had a well-defined process when dropping databases for a client. It went something like this:

  1. Confirm in writing the databases on which servers/instances to be dropped
  2. Take a final full backup of databases
  3. Take databases offline
  4. Wait at least two weeks to make sure nothing breaks in the absence of this database
  5. Drop databases

This is a pretty good and safe method. If taking the database offline causes some unforeseen system to stop working, it can be very quickly brought back online in-place, instead of having to locate the backup and restore it. But it there’s just one problem…

A Classic “Gotcha”

If a database is offline when it gets dropped, its files don’t get deleted. This is because databases that are offline have been closed, and SQL Server is no longer accessing the files for that database. In fact, a common method of moving database files involves setting the database offline, because you can then move or copy the files to their new location. Probably the only downside to SQL Server no longer accessing an offline database’s files is that when the database is dropped, they won’t be deleted. This is well-documented:

If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.


The way I see it, there are two ways to address this unfortunate side-effect:

  • Delete all the files manually after dropping the offline database
  • Bring the database back online before dropping

I very much prefer the latter method, and when done properly the database is online for a very short period of time before it is finally dropped. Either way though, be mindful that dropping an offline database means you have some additional cleanup work to do!

Oct 122018

This week’s PASS Summit Tip is all about food. And with all the learning and networking opportunities you’ll find there, you’re going to need a lot of it!

Have you ever been to a wedding where the food is just “meh”? It’s edible, but nothing great. You can’t blame the happy couple for it, because they’re footing the bill and feeding 100 or 200 people gets expensive quickly. And the food’s just a small detail of the wedding anyway – the real fun has nothing to do with the meal. Hopefully they at least have an open bar….

Now think of PASS Summit, and instead of feeding a few hundred people, they’re feeding a few thousand. And paying convention center prices for it. Yeah, that’s what breakfast and lunch are like at the conference. But that’s okay, because the real fun has nothing to do with the meal here either. (Also, sorry, but there’s no open bar.)

PASS Summit dining has quite the ambiance!

While the conference food may be hit or miss, the real opportunity of dining there is the networking. As I’ve said before, the whole point of being at PASS Summit is to meet new people and make connections. So if you’re heading to the dining hall, don’t go searching for an empty table – find one with other people at it and strike up a conversation. Find out where they’re from, what their job is, and what sessions they are planning to attend today. Exchange business cards, and maybe Twitter handles too! At lunch this is even easier, because there are Birds of a Feather Luncheon tables pre-marked with topics.

My Picks

And don’t forget, if you really don’t want to eat the provided food, you’re in Seattle! There’s awesome choices all around! Yes, there’s a Cheesecake Factory right out the front door of the convention center, but you can do so much better than that. Here’s a few of my favorite places:

Gettin’ my chowder on!

Yes, I know it’s at Pike Place Market, and it’s touristy. But here’s the thing, Lowell’s has AWESOME breakfast. And if you get there right when they open at 7am there aren’t any tourists to be found. The market is super quiet and you can watch the city wake up from the amazing view you’ll get on the third floor. They also have great chowder at lunchtime – get it in a breadbowl and you’ll be ready for your food coma by the time afternoon sessions start.

Biscuit Bitch
I am a connoisseur of biscuits and gravy and its kindred entrées. The best I’ve found in Seattle so far is at Biscuit Bitch. It’s another great way to wake up with food that will put you to sleep during a session. But that’s ok, because you ordered the session recordings, right?

Top Pot Doughnuts
Yes, you need doughnuts, and the ones from Top Pot are really good. Even if you ate a bunch of doughnuts on the SQLTrain, you still need more. So go there and try one, or maybe two. Or just get a box and bring them back to the convention center. A box of doughnuts can help you make lots of friends very quickly! Also, save some for the hard-working PASSHQ staff. While we’re there having fun, they are busting their tails all week. And if you were curious, my favorite type of doughnut ever is a cake doughnut with chocolate frosting (no sprinkles please!)

13 Coins
And finally, if you’re looking for a late night bite to eat after one of the many evening activities lets out, 13 Coins is always a solid option. They’re open 24 hours, but I always seem to end up there in the very early morning for some reason… The original location closed last year, but they moved to a new spot, and I’m looking forward to checking it out.

This is by no means an exhaustive list, but no matter what you’re looking for food-wise, you can probably find it within a reasonable distance of the convention center. I hope you can find yourself some great eats while there!

Oct 092018

This Month’s T-SQL Tuesday is hosted by Jeff Mlakar, and he has asked for us to tell the tale of a project that went horribly wrong.

This will be short, but it’s one of my favorite stories of a project that wasn’t totally thought through. I (thankfully) wasn’t part of the team for this, but the way word spreads in IT, my group heard of this, and we were shaking our heads in disbelief very quickly.

Back in the day, I worked at a large organization with a very large IT staff. There were many systems on differing platforms, and a decent number of them were running Linux. One day we got word that a project had been started to create a custom file system for the organization. Apparently NONE of the many file systems available in Linux met our “extremely specialized” requirements, so we needed our own. Also (and this was really given as a reason for the project) “Google created their own file system, so we can do it too.”

My comment to my team leader at the time was something like “Google has scale requirements beyond our imagination, and has hundreds of employees with Ph.D.’s in computer science and related fields who can address these problems. We don’t.”

I took a different job shortly thereafter, so I never got to experience the results of this project. But I don’t believe there ever were any. I’ve kept in touch with former co-workers and none of them are aware of any custom file systems in use.

Thank you Jeff for hosting this month’s T-SQL Tuesday! If you’d like to help out and host one, contact Steve Jones.

Oct 052018

This PASS Summit tip is all about power. Electricity. Inconveniencing electrons for your own personal benefit. If you’re attending a tech conference, chances are really good you’ll have multiple computing devices with you, and each of those devices will need power. And if you’re like me, you’ll be doing plenty of tweeting and picture-taking, and perhaps your device will be using more power than normal so you may need to charge more frequently. With a little planning ahead and some help from this post, you can make sure all your devices stay charged all day long!

USB Wall Adapters

If you have lots of devices to charge, you’ll need lots of USB ports for charging. A good multi-port USB wall adapter is handy whether travelling to PASS Summit or anywhere else. This is an accessory that never leaves my work backpack. I’ve been very satisfied with this 4-port model from Anker. It charges devices quickly, can deliver 2 amps on each port simultaneously, and can convert the 220V/50Hz power that the rest of the World uses, so no voltage adapters necessary! On top of all that, it’s small and the prongs fold in for easy packing. I really don’t leave home without it!

USB Batteries

These first appeared on the market a few years ago, and I really believe they are essential for conferences. Basically they are a battery with one or more USB ports on it. Charge it up overnight, and then you can use it to recharge your devices throughout the day. They come in varying sizes and capacities, from ones that can literally jumpstart a car (hint: you probably don’t need this) to tiny ones the size of a lipstick tube. I carry two of these with me at summit. One is larger, stays in my backpack and is with me during the day. The second is smaller and weighs less, and that’s what comes out with me in the evenings. You can find plenty of these online; I’ve always had good luck with ones from Anker.

At PASS Summit you will find many places to charge your devices. There’s lots of power strips in the community zone and plenty of other nooks throughout the convention center. Where you won’t find much power is in the session rooms. And chances are you’re not going to want to miss out on sessions so you can charge your phone (even if you did buy the recordings). This is where USB batteries really shine, because you can charge your devices anywhere at anytime. Even if it’s during a session where the nearest outlet is across the room.

Charging Cables

If you’re ordering a USB battery to charge your devices, it may be a good time to get a few additional charging cables as well. I have never been disappointed with the ones sold by Monoprice.

Extension Cords & Power Strips

With all of the above charging accessories, why not add a bit of power distribution as well? If you find yourself needing an extension cord while at PASS Summit you won’t be out of luck, there is a Target a few blocks from the convention center. But if you’re building out a power kit for travel, buy it at a hardware store and save yourself some money. I always keep a 6 foot extension cord in my backpack. It’s nothing fancy, and cost me about $3. Whenever I travel I also keep a 15′ cord in my luggage – this can be immensely helpful at the hotel if there are no power outlets where you need them.

Finally, one thing I carry with me at Summit that isn’t usually otherwise in my backpack is a simple 6-outlet power strip. You never know where you might need one. I’ve also come across situations where a power strip is fully utilized and more people would like to plug in, and then you can be the prepared Boy/Girl Scout who saves the day!

Some of these suggestions may seem like no-brainers, but I can tell you I was most definitely not prepared power-wise for my first PASS Summit. I hope no one else has to suffer that same fate!

Oct 032018

I’ve been honored to present a session at PASS Summit for the past few years, but this year I am especially humbled as I had not one but two abstracts accepted! The first is an introduction to Azure Cosmos DB. The other is covering some of my favorite SSMS and T-SQL tips and tricks that can help you save time!

Select Stars: A SQL DBA’s Introduction to Azure Cosmos DB

Level: 200
Where: Skagit 4 (TCC Lower Level)
When: Thursday November 8, 2018 1:30-2:45pm

Azure Cosmos DB has quickly become a buzzword in database circles over the past year, but what exactly is it, and why does it matter? This session will cover the basics of Azure Cosmos DB, how it works, and what it can do for your organization. You will learn how it differs from SQL Server and Azure SQL Database, what its strengths are, and how to leverage them. We will also discuss Azure Cosmos DB’s partitioning, distribution, and consistency methods to gain an understanding of how they contribute to its unprecedented scalability. Finally, we will demonstrate how to provision, connect to, and query Azure Cosmos DB. If you’re wondering what Azure Cosmos DB is and why you should care, attend this session and learn why Azure Cosmos DB is an out-of-this-world tool you’ll want in your data toolbox!

SSMS & T-SQL Tricks: Working Smarter, Not Harder

Level: 100
Where: Room 6A
When: Wednesday November 7, 2018 4:45-6:00pm

Do you spend a considerable amount of time in SQL Server Management Studio? Are you always in search of ways to write better code with less effort? Attend this session to see demonstrations of all the helpful gems SSMS has hiding in plain sight that will save you keystrokes, mouse clicks, and time while getting your work done. We will also cover useful T-SQL Scripts to make common tasks easier and faster, and solutions to frequently-encountered coding problems. Whether you’re a developer or a DBA, whether you do your work on-premises or in the cloud, attend this session to learn how easy it is to write better T-SQL code and save the day, all while working smarter, not harder!

I hope to see you there!

PASS Summit starts in a little over a month, and if you’re thinking this sounds pretty awesome, registration is still open! The program committee has put together an incredible schedule combining top presenters from all aspects of the Microsoft Data Platform. On top of that, opportunities for networking abound. While the sessions are awesome, the community is PASS Summit’s greatest asset. You really don’t want to miss this.

Even if you can’t attend, be sure to follow me on Twitter (@SQLBob). I’ll be sharing plenty of updates and photos from throughout the conference. Even if you can’t experience PASS Summit in person, by following myself, other community members, and the #PASSsummit2018 hashtag, you can get a great virtual experience.