Recent Articles:

Wendy Pastrick and Grant Fritchey: Optimists in Action #passvotes

wendyIf I had a favorite kind of politician, it would be the kind that, when asked a question, sighs tiredly, and then regains their gleam of manic optimism as they begin to speak about the path ahead.

Given that, my two favorite politicians right now are Wendy Pastrick and Grant Fritchey.

Both of these people are realists, with that unaccountable combination of caution and optimism that I like in friends and technologists alike. Both give to excess to the SQL community. Wendy  speaks, volunteers, works with virtual chapters and user groups, acts as a regional mentor, and joined the PASS Board in 2013. She’s a PASSion Award recipient. Grant speaks, organized and ran a user group, volunteers, has edited the SQL Standard magazine, and on and on.

I’ve talked to multiple board members, past and present; it’s a difficult job. I support Wendy and Grant because they can do the job, do it well, and make a difference. And when their terms are done and we ask them how it went, they will sigh, and then get that manic gleam again…

grantGet information about the PASS elections here, and look for your ballot in  email  around September 24 or so. Vote!

Happy days,
Jen McCown

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 972-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

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/