Archive for April, 2009

My PowerShell Script Won A Contest!

2009-04-30

Yesterday, I attended a Live Meeting for the PASS Database Administration Special Interest Group, presented by Buck Woody. The topic was on using PowerShell with SQL Server. Given that these are two of my favorite technologies, I decided to participate.

Since it was an introduction to PowerShell, the information was a bit below my skill level. However, I did glean a few gems of knowledge. Most of all, I found Buck’s presentation style very appealing. Now I’m really going to have to listen to episode 41 of SQL Down Under.

Anyway, at the end of the presentation, Buck announced that, courtesy of SQL Server MVP Rick Heiges, a Microsoft Thrive card would be awarded to the person that sent Buck the best PowerShell/SQL Server script.

I wrote a utility for myself back in January. Sometimes when I get an escalation call, the front-line support personnel simply jump in with “I received an alert with error ___.” Very few error codes are committed to my memory, so this doesn’t provide much information to me. My utility simply looks up the error message in the [master].[sys].[messages] table and then presents it in a web page.

I’ve been planning to publish the script utility on this blog for a little while, but just haven’t gotten around to it. It’s simple, but pretty useful.

I decided to submit it for Buck’s contest, and (amazingly) I won! Woohoo! Check out the announcement on Buck’s blog: And the Winner is – Get SQL Server Error Messages from PowerShell.

When I redeemed the Microsoft Thrive, I received a voucher for a free certification exam. I need to update my certifications, so this is very serendipitous. The bigger deal, though, is the recognition.

Tags:,
Posted in PowerShell, SQL Server | Leave a Comment »

A solution of my very own

2009-04-13

Typically, when I’m faced with a SQL Server problem, I perform research until I discover a solution. I have many sources for research… here are a few (and the order that I typically follow) for troubleshooting specific error codes and messages:

  1. [master].[dbo].[sysmessages] (SQL Server 2000) or [master].[sys].[messages] (SQL Server 2005 and later)
  2. The Microsoft Knowledge Base
  3. Books Online: SQL Server 2000, SQL Server 2005, or SQL Server 2008
  4. Search engine
  5. Newsgroups and forums
  6. Any of several colleagues

Regardless of the source, I submit query after query and constantly refine my search terms. After sifting through enough results I will have the answer. Sometimes it’s presented to me in complete form, other times I must synthesize the solution from several partial answers. I have been remarkably successful as a DBA just from honing my research skills.

As my knowledge of SQL Server deepens, there have been those rare occasions in which I come up with an answer of my own. An escalation call on Saturday led to such an occurrence.

NOTE: This escalation call dealt with a SQL Server 2000 instance, so that is the version that I’ll be targeting with the various links.

A customer had rebuilt all of the indexes in a ~300 gigabyte database and then they were surprised when the data files grew. One of the data files had doubled in size but was only using half of the space. This was leading to free space alerts on the volume hosting a few data files (the alert threshold was 15% and the growth resulted in only 8% free space on the drive). The customer requested that we shrink the files.

I’m not going to get into the many reasons that free space in a data file is beneficial or the many issues with shrinking data files.

I decided to start by running DBCC SHRINKFILE with the TRUNCATEONLY argument. I received error 3140: “Could not adjust the space allocation for file ‘%ls’.”

As usual, I began to research the error message using the above-mentioned resources. I found many people asking about the issue, but the best answer applied only to SQL Server 7.0.

I’m not sure why, but I decided to stop researching and try to figure it out myself. Since the error message relates to space allocation, I decided to run DBCC CHECKALLOC. The command finished quite quickly with “CHECKALLOC found 0 allocation errors and 0 consistency errors in database ‘RedactedDatabaseName’.” at the end of the output. I considered running the full-blown DBCC CHECKDB when I had an idea… what if the problem was a variation on the SQL Server 7.0 bug that was still present in SQL Server 2000? Since the known bug related to index allocation discrepancies, I wondered if running DBCC UPDATEUSAGE would help. Other than the somewhat trivial server resources that it would take to report and correct inaccuracies in the [master].[dbo].[sysindexes] table, it couldn’t hurt, right? So I gave it a try. DBCC UPDATEUSAGE took eight minutes and twenty-one seconds to run and made 238 corrections. A subsequent execution of the DBCC SHRINKFILE command (with the TRUNCATEONLY argument) finished in six seconds and returned 5.72 gigabytes to the file system. My hunch paid off.

It was my own understanding of indexes and experience with the DBCC UPDATEUSAGE command that ultimately provided the solution to this problem! It’s so gratifying when answers come as much from me as any other source–a situation that grows more common all the time.

As far as the escalation call, I wrote an email to the customer and laid out five reasons not to shrink the file any further. The customer agreed and the disk space threshold is being changed from 15% down to 10% (which is perfectly reasonable in this case).

Finally, two PSAs:

  • Please consult an experienced database professional before implementing widespread index maintenance. Index maintenance is very important but it is also costly and can lead to disk space issues (data file and transaction log growth). Improper fill factor values for your workload may actually make performance worse.
  • Please consult an experienced database professional for database file capacity planning. In a well designed/managed system autogrow should not occur (being a fail-safe mechanism only) and files should not be shrunk.

Tags:, ,
Posted in SQL Server | Leave a Comment »

I’m Finally Catching Back Up

2009-04-09

March was an incredibly hectic month for me. Actually, February was too! In light of so many other commitments, this blog was one of several endeavors that had to be put on hold. I’m finally getting caught up, though, so it’s time to get some entries posted!

Why have the last eight weeks been so busy?

In February, I had invested somewhere between forty and fifty hours in my first user group presentation, Refactoring T-SQL Code for Better Performance. I took a few days to catch up on my rest (and the various things put on hold because of the effort). Then, about a week later, I accepted the opportunity to speak at the March meeting for the other local user group, Greater Fort Worth SQL Server Users Group. This presentation didn’t require as much preparation, probably closer to fifteen hours, but it still required a significant investment of time. I’ll write more on the presentations in upcoming posts.

On the work front, I have been nursing a few longer-term tickets. Obviously, in a service desk environment, we want to close our tickets as quickly as possible. It’s always nice to take a call and quickly resolve the issue. However, I enjoy performance and in-depth troubleshooting tasks (such as root cause analysis). Unfortunately, while very intriguing, they also take a great deal of time and effort to complete. A colleague insists that I’m too thorough, and he’s right, but it’s hard to pass up a chance for some good SQL Server sleuthing! Also, I want to provide certainty to our customers and actionable recommendations that keep the situation from recurring. So, in early March, it just ended up that quite a few of these larger tasks landed on my plate. Then two of the four guys on my team took off the same week for spring break. For the first time at this job, I started feeling overloaded. Fortunately, my teammates jumped in and helped clear up a lot of the back log.

Finally, my wife and I adopted a puppy in the middle of the month. Our sleep schedule has definitely suffered from this, as we all grow accustomed to each other. By the way, I am tremendously impressed with the volunteers at the Miniature Schnauzer Rescue of North Texas. They were so friendly and helpful in finding the right addition to our family.

Posted in Uncategorized | Leave a Comment »