Meme Monday: I Got 99 SQL Problems And the Disk Ain’t One

This month, Thomas Larock (Website| Twitter ) declared a meme Monday inspired by the Hugo song 99 Problems - aside from disk issues, name 9 problems you frequently see in your shop which are not related to disk issues.

1) Using default install settings for file growth
Despite numerous examples from live systems showing that those settings are not appropriate for our product's databases, we frequently see new customers with all their databases set to the default 10% growth setting, despite the statement in the "best practices" documentation that tells them otherwise.

2) Bloated SQL error logs
Many times when customers report having issues and we're called in to examine what's happening with their SQL server, we find that we can't open the SQL Error Logs because the customer's SQL server hasn't been restarted in a long time and the errorlog is so bloated that it's too big for the UI to open in a timely manner. The simple fix, of course, is to set up a SQL job that runs sp_cycle_errorlog periodically.

3) Not doing ANY index maintenance
Frequently, when I hear about SQL performance issues, I find that the customer has turned off the regular index maintenance jobs "because they take too long". Eventually, this results in painfully out of date statistics, severely fragmented indices and terrible performance.

4) Shrinking databases as "maintenance" to "free up disk space"
I try my best not to use profanity or to scream (loudly) when I see this enabled on customer servers. I just take a deep breath and forward the following links to the guilty party:
Paul Randal: Why You Should Not Shrink your Data Files
Brent Ozar: Stop Shrinking Your Database Files. Seriously. Now.

5) Developers "testing" code on production
Don't get me started....

6) Poor backup plans not even close to SLA requirements
High volume OLTP Production database, full recovery with log backup once a day at midnight and full backup once a day at 1AM - but their SLAs say they have to completely recover from failure within one hour. They claim that because the SQL server is clustered, that they don't have to back up the databases more often. Really? Please don't call me when things go south.

7) No disaster recovery plan
... And office in the middle of Tornado alley. Again, please don't call me to resurrect your SQL server when your data center gets destroyed by one of the 500+ tornadoes that went through town. You don't have a copy elsewhere and I can't create something from nothing.

8) Letting idiots have access to the Server room
Believe me: I can't make this one up - it actually DID happen.

A particular person on the night cleaning crew entered a server room to vacuum it. Because the vacuum's cord was too short to allow him to vacuum the far side of the server room, he unplugged something to free up an outlet so he could vacuum the far corner of the server room. The "something" he unplugged was the main power for the SQL server, which knocked out the customer's website until someone entered the server room in the morning and noticed that the server was unplugged.

9) Not having automated monitoring on servers
You'd think this was obvious, but I've been called too many times to count late at night to hear that someone's server is "down", only to find out the reason the SQL server crashed or the backups failed was because the disk was full. Automated disk monitoring systems have been around for over a decade, yet many of our customers don't have any automated monitoring and I doubt that their IT people check the servers every day since they seem so surprised to discover that their disk has filled up completely.

After just thinking about those 9 items, it's time for a stress pill.

SQLRally - Birds of a Feather

Earlier today, Jorge Segarra (twitter) sent via twitter a request for volunteers to head up Birds of a Feather tables at SQL Rally. I asked him if he had someone to head up a PASS WIT table.

He replied, "@LadyRuna if you're volunteering, you could host it :-D #sqlWIT #sqlRally"

So it looks like I'm hosting the WIT table at the Birds of a Feather event at SQL Rally. Please stop by and say hello if you're there. I've never run one of these before and I'll be making things up as I go along.

Hooray for Wonderful Husbands

I was talking to my boss yesterday about my professional development plan for this year, and the subject of training came up. He said we only had enough money in the budget to send one person to a conference this year, and since I’d attended SQL PASS Summit last year, they were going to send my coworker this time – meaning I wasn’t eligible to attend any offsite training at all. He suggested I find some books to read to fulfill my professional development plan.

Needless to say, I was not at all thrilled, and mentioned it to my darling husband.

He, too, was unimpressed. He knew how important and useful attending SQL PASS Summit had been for me.

Over dinner, he casually asked me, “Do you think you can take time off from work the second week of May?”

I checked my calendar – I was not scheduled to be on call, so I could take time off.

I replied, “Sure. Why?”

He held my hand and said, “There’s this PASS SQLRally thing in Orlando that week. We have enough vacation money saved up to send you.”

(Queue romantic music and sappy romantic scene….)

Ok, get your mind out of the gutter… The final result is: I’m going to PASS SQLRally and even get to attend one of the Pre-con classes. Hooray for wonderful, amazingly loving husbands! I certainly picked the BEST husband in the world. See you at PASS SQLRally!

Mater Immersion Event Competition (Entry)

As an amazingly wonderful contribution to the SQL community, Paul Randal (blog | twitter) has offered the opportunity for a member of the SQL community to win a free seat at the SQLskills.com 5-day Internals and Performance class in Dallas, February 21-25. To qualify, one has to state in a blog post why you want to come to a class taught by them and why you'd make the best use of the knowledge you'll get from being in the class. I've decided to throw down the gauntlet and accept that challenge.

What would deep SQL Server training provide me?

Although I've been working with SQL server for about 14 years, for the most part it's still a black box. I'm often required to quickly respond to performance and corruption problems related to critical SQL Server systems belonging to my company's customers. I often feel as if I’m staring down the barrel of Mon’s Meg (see picture at right) as I strive to think up what to try next to solve the issues. Normally I wind up following the "well, last time we did -this- and the issue went away..." 

For many types of issues involving SQL Server, using past experience to solve them without fully understanding exactly how SQL server functions can produce acceptable results - that is, the issue is cleared, but I cannot explain the failure, nor can I can't elaborate on why what I did worked or why the database failed the way it did.

I often feel as if I’m an archeologist on the Shetland Island unearthing another broch. I know how to handle the items to reduce or prevent damage to them, but may not necessarily have a full understanding of what they are or how they’re really supposed to be used.

Understanding how SQL server stores and retrieves data is crucial to efficiently finding solutions to complex issues within extremely aggressive SLA timeframes. This is even more important when users are wanting to take advantage of the new features of SQL 2008, because structures such as data compression, sparse columns and Filestream can drastically impact a server's ability to perform as the users wish it to.

Many times when I’ve needed quick assistance with solving database issues or explaining to yet another customer why one should not shrink databases daily as “maintenance,” I’ve referenced Paul’s blog to provide the explanations for me. I’m hoping that by attending this training, I will learn what I need to know so that I don’t feel so sheepish when defending the necessary fixes for SQL issues.

I would make use of the knowledge learned in Paul & Kimberly’s class almost every day at my job. It could also help me advance from just another regular SQL person to a senior level or even expert level SQL person.

Finally, I have a friend living about an hour outside Dallas, TX who has a mixed herd of Barbado sheep and fainting goats – proving that sheep and goats can live together happily (Paul knows I have 5 goats).

*ALL photos taken by me on our trip to Scotland in July 2000 (well, except the last one, which was taken in 2009 at my friend’s place in Texas).

Un-SQL Friday: My Tech Giants

It’s Friday, the end of another long work week, and just as we have the monthly T-SQL Tuesday in which all SQL people are encouraged to post something related to a particular topic, the female half of Midnight DBA, the dazzling newly-minted MVP Jen McCown (blog | twitter) has declared this Friday, “Un-SQL-Friday”. For this Un-SQL-Friday, she asked us:

“Read this blog, and then write whatever you want about Tech Giants. Be sure to mention in your blog that you’re writing for Un-SQL Friday, and link to this post. Oh, and have it up any time before the weekend (Saturday Jan 22) hits, mmkay?”

Well, that’s an interesting question. I suppose another way to put it is,

“If you were asked to fill the shoes of __<name of tech giant>_ , could you do it?”

I think the picture to the right answers the question quite succinctly as “Well, I can plug the top of his boot, but I’ll need a lot of other material to fill the rest.”  That is, I may not be at the same skill level on that topic as the one who wrote the book on it, but with enough effort and time I could get there. Much of the “greatness” comes from that person having researched and documented something that I haven’t yet encountered. The "SQL Gurus” are the ones who wrote the books I’m buying or actually worked on making SQL server what it is today.

I could rattle off a litany of names (as some of the other un-SQL participants did), or I could

SELECT TOP 10 GuruName FROM SQLServerGurus

and see what that returns to me. Or I could direct you to the listing on the right-hand side of my blog which includes links to many of the SQL blogs that I frequent. Either way, you’ll see that there is no shortage of knowledge available and I thank each and every one for all of the information that they’ve freely provided which has made my job much easier to do.  

t-sql-tuesday-resolutions



TSQL2sday Linkback



Happy TSQL Tuesday Everyone!

This week's TSQL2day is hosted by the lovely Jen McCown (blog | twitter ), the female half of the dynamic duo of SQL MVPs known as the MidnightDBAs. The theme is to post about our technical resolutions for 2011 – what we hope to accomplish and why.

It seems that a lot of us are making similar resolutions for 2011, so if you think you've seen this list before, you probably have. I'm not copying other people's lists, it's just that many of us in the SQL Server Community are seeking to accomplish similar things this year (birds of a feather and all that).

RESOLUTION: BLOG at least once a Month on SQL topics

I started my blog in July 2010, and have been rather lazy about updating it. In 2010, I posted 15 articles, of which only 1 was 100% about SQL. I did have 6 others I tagged with "SQL" or "SQL PASS", but they were not focused specifically on SQL. That's not a particularly good record, especially since the purpose of starting this blog was to write about SQL and NOT about sewing, random thoughts or my goats, even though those are excellent topics for blogging. The good news is that by posting this article, I can check off January's SQL post...a good start to any set of resolutions. Of course, I DO have at least 11 other SQL blog posts that I must create.

RESOLUTION: READ Books on SQL Server 2008
AND
POST reviews of the SQL Server 2008 Books on my BLOG

This is a dual resolution, thanks to Brent Ozar (blog | twitter), who very kindly sent me a copy of "Professional SQL Server 2008 Internals and Troubleshooting"  which he co-authored with 6 other people. As a condition for giving it to me, he asked that I write a review of it. (Brent: I'm reading it now - really!!). Reading books on SQL will certainly help me better understand the inner workings of SQL and hopefully advance my career. Blogging about what I read will not only ensure that I absorb the information I read about but also will fulfill my blogging resolution.

RESOLUTION: WRITE a PRESENTATION on a SQL Topic
AND
Volunteer to PRESENT on a SQL Topic

As a SQL professional who has been working with SQL server since the days of SQL 6.5, I really ought to volunteer to present at a SQL-related event - be it a SQL Saturday, SQL User Group meeting, 24 Hours of PASS, or PASS Summit itself. This resolution is a two part resolution since the second part won't happen unless I actually have something prepared to present. I've been hiding in the background whenever calls have gone out for volunteers to speak because I "have nothing to say" and "have no idea what I can present about." Anyone who has heard me talk about sewing and costuming at the Science Fiction and Anime conventions that I've been attending knows that I have plenty to say (well, plenty to say about sewing, costuming, and a few other hobby-related topics) and don't fear getting up in front of 50 or so people and talking their ears off.

RESOLUTION: Become a SQL MVP

This is likely the hardest one for me to accomplish - especially since it requires others to recommend me for the award. However, if I work to exceed the other goals listed above, I might actually have that within reach.
 
What do you think? What are YOUR resolutions for 2011?

Corrupt Database Adventures

Ever notice how a “quick question” can turn into a major project? A coworker IM’d me earlier today with a “quick question” asking whether I knew how to get a database out of Emergency Mode.

Uh oh…

She then reassured me that it was a DEV box and not production. Whew! That meant that we didn’t have to worry about losing important customer data and at worst we could restore from back up without too much pain. I decided to investigate since it’s not often that one gets to fiddle with a genuinely corrupt database that’s not critical to the bottom line.

I did a quick web search and found an article by Paul Randal that talked about the last resorts that people try first with a corrupted database. It mentioned that one thing people try is rebuilding the transaction log  using  DBCC REBUILD_LOG. I tried it, but it’s not a valid DBCC command for SQL 2005 (in fact, if I’d read a few more paragraphs in Paul’s article BEFORE trying it, I would’ve seen him mention that very fact – that should teach me the folly of skimming articles).

The next item mentioned was to perform a DBCC CHECKDB using the REPAIR_ALLOW_DATA_LOSS setting. What better time to try this than when you have a broken dev box to play with? Take a deep breath and see what happens.

DBCC CHECKDB (testdb, repair_allow_data_loss) WITH NO_INFOMSGS;

The results said:

Msg 605, Level 12, State 3, Line 1

Attempt to fetch logical page (1:2576) in database 5 failed. It belongs to allocation unit 72057594060406784 not to 281474980642816.

Msg 8921, Level 16, State 1, Line 1

Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

That didn’t sound promising.I tweeted #sqlhelp quoting the text of the Msg 605 to see if anyone else had any suggestions. Meanwhile, I searched for other possible tricks to try.

One posting I found on a SQL newsgroup (forgot which one and can’t find it again) stated that Msg 605 indicates a physical drive failure. I didn’t verify that, but since the dev box had been troublesome for quite some time, it didn’t sound too inaccurate.

Meanwhile, I got some suggestions from #sqlhelp to try DBCC DBREINDEX and DBCC CHECKTABLE on all the indexes and tables. I snagged a script from SQLServerCentral that loops through every table and performs a DBCC DBREINDEX on all indexes. Lots of black text scrolled through in the results pane, interspersed with some shocks of red. The red means something was wrong – but at least the black text following the red text indicated that things were repaired.

I then ran the DBCC CHECKTABLE using the same script as above to loop through all tables. It crunched through the tables happily for about 75% of the database, then it stopped with an error:

Msg 211, Level 23, State 51, Line 13

Possible schema corruption. Run DBCC CHECKCATALOG

Sweeeeeeeeeeeeeeeeeeeeet. An “Improvement”? No. I ran DBCC CHECKCATALOG as suggested. It completed successfully. THAT seemed promising. I tried rerunning

DBCC CHECKDB (testdb, repair_allow_data_loss) WITH NO_INFOMSGS;

Same results. One more time with gusto – this time I noticed that I’d lost the SQL connection. Reconnect, retry, and it disconnected before completing the command. The server had degraded sufficiently that there certainly was no hope of resuscitating it, so I logged off. A short while later, I received a message stating that they’d located the backup image of the dev box and were ready to rebuild it.

Although I spent several hours working on this, it was a great learning experience for me – most importantly, I learned that if DBCC CHECKDB can’t repair it, then likely you’re best off restoring the database from backup.

Before you find yourself in a situation with a corrupt database, take a look at Paul Randal’s “CHECKDB from Every Angle: Emergency Mode Repair”, which is an update to his article mentioned above. I wish I’d found that one first, but one uses the tools that the search engines return. Actually, the better choice would be to simply read through Paul Randal’s “Corruption” Category in his blog – this is the ever-growing set of articles that he’s written about database corruption. I’m going to spend a lot of time reading through those posts, and you should, too. It’ll certainly make you life easier when someone asks you a “quick question” on how do you fix a database in Emergency mode.