Recent Articles:

To Mourn a MidnightDBA, Press 2

September 17, 2014 Fluffy Stuff No Comments

finalpostcropWe had an interesting time on Tuesday evening. (Any chef worth his salt knows that interesting usually isn’t a good thing.) The interesting part was when Sean’s blog reported that he died in a car wreck. Sean was very surprised to hear about this.

My first indication that something was awry was a text from our dear MrDenny, asking if everything was okay. People were worried.

Worried about what? What blog? Oh, hellfire.

First things first: tell Denny, and the dozens of people messaging me on Twitter, that everything’s fine, Sean’s fine, we’re all fine, oh my goodness I’m so sorry for the worry but we’re fine. Step two was pulling down the post.

After the anxiety comes the relief, and after that comes the dark humor. We’re into dark humor, so we’re not going to let this weird almost-crisis, courtesy of your friendly neighborhood hacker, go. Not quite yet.

Here’s what we’re gonna do

We’re throwing Sean a wake this Friday on the webshow. He’s pretend-dead, so we may as well pretend-mourn him and pretend-celebrate his life. BYOB.

You? Oh, here’s what you need to do, and you don’t even have to stay up late, if you don’t want to. YOU can call in to our Skype voice mail box – that’s 927-815-1DBA. (Remember how to punch in letters for numbers?) So yes, you should call in and say whatever you want said at his wake. Er, his fake-wake. Fake-bake-wake. Wakka wakka wakka.

Call the number. Leave a message. We’ll play some of them on the show: DBAs@Midnight episode 7, live this Friday at 11pm.

I suppose if that’s too much work, you can leave us a tweet at the hashtag #MCM1up (Get it? 1-up? He’s got a whole new life? GET IT??)

Poor taste?

Listen, life is long, and it’s also too damn short. Someday one of the two of us is going to be gone, and the one that’s left is going to be lost and sad, along with (as it turns out) a whole hell of a lot of friends and well-wishers. While we’re all still here, let’s have a little fun.

Tip: RECOVERY_PENDING is not the same as RECOVERING

I’ve seen this mistake more than once, from DBAs with a middling to fair amount of experience. So it bears repeating. RECOVERY_PENDING is very different from RECOVERING.

Definitions

RECOVERING means that things are happening, and all may be well. “Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.”

RECOVERY_PENDING means that something is wrong, and YOU have to fix it. “SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.”

(Definitions from the Technet article “Database States“.)

Finding the database state

Finding the state of your databases is easy. You can look in Object Explorer, and it’ll say so right there:
recovery_pending
recovery_pending2

You can look in sys.databases, which is the preferred method:

SELECT name, state_desc
FROM sys.databases;
– Optional: WHERE state_desc <> ‘ONLINE’;

(Why is that preferred? Because it’s programmatic, and so repeatable and fast and usable in automated scenarios. And ot’s more accurate, as the GUI can glitch or cache information, whereas sys.databases never never does. )

And of course, you can look in the SQL Server error log and see if any errors popped up while recovering databases (for example, during SQL Server startup, or when the database was set online).

When good databases go RECOVERY_PENDING

recovery_pending3

One of the more common scenarios for RECOVERY_PENDING is that a database file is missing, or renamed, or moved, or who knows what. That’s definitely the place to start. After all, the error log – remember the error log? – will often tell you that this it the problem. Look here:

FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Demo.mdf for file number 1.  OS error: 2(The system cannot find the file specified.).

In this case, it’s very clear; it can’t find the file. As it turns out, that’s because someone renamed the file while the database was offline (either while SQL was stopped, or the DB was in an OFFLINE state). So I renamed the file properly, set the database to ONLINE, and all was well.

You’ll occasionally get RECOVERY_PENDING after a service restart, for no good reason, and I don’t know why. SQL Server gives you error 17204, “Open failed: Could not open file …. OS error: 32(The process cannot access the file because it is being used by another process.)” And then error 5120, which carries the same message.  This is nearly always resolvable by setting that particular database offline and online again.

Stop. Rebooting. The server. Durnit.

Note that I do not recommend rebooting the server, or even restarting SQL. It’s a problem with a single database. I’d very much like this message to get out there: Most problems in SQL are solvable without a restart!  Especially THIS problem.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

SQL Sins and Powershell scripts, from another lovely #SQLSatOKC

August 25, 2014 sql server 2 Comments

Hi-ho there, Jen the DBA here. We spent a lovely weekend in balmy (99F and 30% humidity) Moore, Oklahoma for SQL Saturday #309.

On Friday, Sean and I gave our “Become an Enterprise DBA” precon, and had dinner with the natives. Thanks again, OKC UG, for the dinner and beer! Friday night we had our regular DBAs@Midnight, titled “3 Rants”, where we talked about the new Coke* campaign, and the darn SQL whippersnappers these days, and that companies and orgs really need to STOP REBRANDING CONSTANTLY BECAUSE THAT’S A REALLY BAD IDEA. I’m looking at you, “SQLPASS-not-SQL Server“.

Ahem.

T-SQL ThrowDOWN

Saturday, we attended our very first T-SQL Throwdown, hosted as always by its creator, Hakim Ali. I had been recruited as an “Expert for Hire”, though no one wound up “hiring” me…and this was so much fun! Hakim tells the attendees to form groups of 2-4, with at least one laptop (with SQL installed) among them. Some people choose not to participate, but that’s fine, as it turns out that coding actually IS a good spectator sport.

He passed out USB sticks with the password-protected questions, and began. He revealed the password for the first question, and the teams got right to work. As a team figured out the answer, they’d come up to the front to type it in on the display. If they got it wrong, the next team in line – if there was a team in line at all – could try. The points, and the difficulty, ramped up question by question.

It was great. Some teams would get most of the way there, and fail on a typo. Others would get the answer NEARLY right, but leave out one crucial detail. Nobody was requesting expert help (it costs half the question’s points for an expert), so he made the experts free for teams that hadn’t scored yet.

After 10 questions, the winning team just barely took the lead, and took home the prizes. I was really pleased with how entertaining and engaging the whole event was. (I’m going to push him for a Celebrity Throwdown sometime…)

okcposhOur Sessions

Sean presented his RegEx for DBAs session (here’s the code) to fill in for a last minute cancellation, and then presented DIY Performance Reporting (demo code) as the last session of the day.

I presented both Code sins, and my new Powershell Cmdlets for DBAs session (download the code file here), which seemed well received.

Thanks very much, OKC, for a fantastic SQL Saturday!

Happy days,
Jen McCown

http://www.MidnightDBA.com/Jen

 

*I don’t mean New Coke. That’s just not okay.

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?

September 13: SQL Saturday Kansas City
November 3-7: PASS Summit, Seattle, WADecember 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

Blog Posts by Category

DBAs@Midnight

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

The best database career advice you’ve never heard!

DBARoadmap.com

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/