Most IT professionals lean on either knowledge, instinct or some mix of the two to solve the problems that they encounter. Knowledge and instinct are powerful tools that develop with experience. In the case of knowledge, we can rely on the experience of those that are gracious enough to share their experience via books, blogs etc. to make us all stronger. Instinct is a much harder developed tool. There is no way to do a Bing search to see if you have experienced a similar situation before and how you reacted to it.

What happens if we encounter a completely new problem that nobody has ever experienced before? Instinct and knowledge both require experience to move forward but there is none to draw from. At this point there is a real chance of getting caught up in what I like to call the Reaction Cycle. The Reaction Cycle is the technological quicksand that waits for us outside of our knowledge or when we are misled by our instincts. We apply an opposite force without thought, iteratively making things worse.

Think of the Reaction Cycle in terms of a brand new junior DBA. One day while the rest of the team is off at lunch they start getting alerts from the production sales database. From the alerts it appears that the server briefly lost its connection to the SAN and now there is some database corruption. The junior DBA, seeing all of the disk errors, decides the box needs a reboot while most of the company is still at lunch and proceeds to kick it over. When the server comes back up there are multiple inaccessible databases. By this time help has arrived, the rest of the team has returned from lunch. They quickly decide that at this point the best option they have is to restore the databases. They will lose some data but since this happened over lunch it is not the end of the world. They all agree that this is much faster than trying to work through the database corruption; after all they need to react quickly before too much money is lost. As they are restoring the sales database they discover they cannot apply transaction logs after 4 AM. A little further digging reveals that the junior DBA reacted to an alert about a log filling last night by truncating the database log. At this point the production database is gone and they have to go with what they have. 8 hours of sales are now gone due to the Reaction Cycle.

Shrinking databases might be said to kill kittens but reacting can definitely kill a career. When something goes wrong, your first step is to collect information. What is wrong? What do the logs say? If you have an error message then what does Bing say about it?

The next step is to process the information that was gathered. Based on the facts and data collected you can begin to formulate a response to what is going on. Sometimes based on this you need to go back and collect more data, like if you decide you need to restore you may want to try out the restore to a development server to make sure you have all the steps down.

Finally, after all the collecting and processing it is time to respond. What we are doing here is making a carefully planned move based on based upon the collection and processing we have done in the previous steps. We almost always know at least our next move if not the next couple of moves and we have thoughts on what may go wrong and how we would respond to that.

After responding we start over with the collection phase and move into the processing phase. Did we get the results we wanted? Are things better or worse? We continue to cycle through this process until we find no response is necessary.

With that, it is time to unveil the Collect, Process, Respond methodology for troubleshooting. In simple terms, we want to gather all necessary data to develop a plan then execute it. Feel free to print the image below and hang it on your cube wall to remind you to use the methodology.

Now that we have covered why it is important to have a methodical approach to problem solving and taken a high level look at the Collect, Process, Respond methodology it is time to start digging into the individual phases.

The first phase we are going to dig into is the Collect phase. The most important thing about the Collect phase is that only 1 person does the data collection per system. With multiple people collecting data from the same system the risk of reacting to monitoring induced symptoms goes up exponentially. A good example of this is if someone fires up a Profiler trace on a server to collect data about an issue while someone else is looking at active user sessions. The person monitoring user sessions may see the sessions start to pile up but not get back to the source of the waits before the trace is stopped. This could lead to user sessions piling up being misattributed as a symptom of the problem rather than a necessary byproduct of troubleshooting. If it is truly necessary to have more than 1 person collect data from a system then 1 person has to call the shots and everyone has to communicate well.

The Collect phase is where we start scoping the issue or figuring out how wide to cast the net. I like to refer to it as ruling things in. We want to err on the side of ruling things in because it is easy for knowledgeable people to rule them out later. Remember our goal here is to figure out what is wrong.

So how do we go about actually collecting data? I like to ask these questions:

The starting point for the Collect phase is to look at what the symptoms are. This is a key question because it helps us figure out what to collect. The symptoms may even tell us exactly what is wrong. If users are reporting a SQL Server error message about an account being locked it then it becomes easy to know what is going on and we can jump to the Process phase.

Once we know the symptoms we want to look at what locations are involved. This is very important because it helps define the scope of further collection activities. If all users in a particular location are having issues then we would want to focus on what is unique to that location, but if all users in the company are having issues then we would want to look at what is common to all.

Based on the answers to the previous questions it is now time to start looking at what systems might be involved. The goal of this step is to find all of the moving parts that make up whatever activity it is that is failing. This step often includes web servers, network load balancers and SANs so it is important to start bringing in other teams at this point.

The last of the generalist tasks to get through before really digging in with the tools that we are comfortable with is to review the change control history. Many organizations have a calendar on a wiki or on SharePoint, sometimes there is even a log at the NOC in organizations large enough to have one. Worst case, talk to the primary on-calls or managers of systems that you may be involved to find out what changed lately. Spend some time here; almost everything that goes wrong is because of a change that someone implemented.

Finally, we get down to the part that DBAs love. We get to bust out our magical tools that we are so used to using. I always save this for last because it is easy to get lost in the data, especially if it feels like it is leading us somewhere. This is where you would go trolling on any involved servers looking for any data that supports what you have seen earlier or anything that does not match with established baselines or in absence of formal baselines then anything that does not look the way you are used to seeing it.

There are a number of third party tools to automate information gathering. Microsoft has the Management Data Warehouse and the Performance Dashboards. The great thing about these tools is they tell you at a glance what the important metrics are and usually have some sort of indicator when things are bad.

Remember to be careful not to react while collecting data, the idea here is to gather as much useful information as possible.

At this point we have collected all of the information we think we need and it is time to move into the Process phase. This is most people’s favorite phase because it feels the most like solving the problem but it is important to remember that we are not pushing any buttons or pulling any levers at this point. This phase is all about the making a plan to address the issue. You want to come away from this phase with an action plan, an expected result and a plan to rollback whatever action you take in case it makes things worse.

As we move from the Collection phase to the Process phase we need to ask these questions:

There is a reason that I have listed these questions first. These are the things that will let us short circuit out of the Collect phase. It pays to keep them in mind when working through the Collect phase to avoid prolonging outages with unnecessary analysis. A good example of a short-circuit out of the Collect phase would be if one of the symptoms was an error message stating that a SQL login was locked out. The problem is clear and the solution is simple and low risk. The follow-up monitoring is simply to make sure that the account does not lock out again. The trick here is to make sure that your intentions are in the right place. Be especially aware of decisions made to make you look good or avoid looking bad.

Next we have to look at the data we collected to see if any patterns can be identified. As patterns emerge theories will develop. It is important to create at least one test for each theory. I say at least one because we may have multiple moving parts and each moving part should get a test. Say that a client application hosts a Reporting Services report is slow, we would want to first run the report by calling it directly in a web browser. If it is slow in the browser then we would want to start looking at the parts that make it up, eventually pulling out individual queries and running them in a query window. We will eventually get to what the problem is.

A clearly defined problem almost always indicates what corrective action is necessary. To keep with the above example we may say the report is slow because the query to get customer orders for the last 10 years is missing an index. There are times when no matter how well defined the problem is the answer is not clear. In those cases having a clearly defined problem is invaluable in enlisting external help whether they are from another team or from a vendor. It is the only way to make sure you are asking for the right kind of help.

Once a list of possible actions is developed it is important to stack-rank the possible solutions by likelihood of success. The goal is to try the action that is most likely to resolve the issue while exposing you to the smallest amount of risk. I always recommend trying things out in another environment first. It helps get the steps and timing down and it exposes weaknesses in the plan so that the plan can be properly ranked. Think about this in terms of a situation where you have unrecoverable database corruption and short circuit all the way to this step, would you immediately start a point in time restore to production or would you try it out on another server first to make sure that your backup is good and that you have a bullet-proof script?

The last thing to do before moving on to the Respond phase is to define how to measure whether the change helped or made things worse. I like to define a measure for each benefit and each risk that I identified while ranking the possible actions. I might say that adding this index will reduce reads from 10,000 to 6 or that adding this index may cause inserts into the table to take longer. I may also say that if the index does make inserts slower and page splits are noticeably higher then I may alter the fill factor of the index. It really pays to define success and failure here to make it clear when to stay, when to rollback and when to tweak the implementation.

More than anything, you really need to make sure you have thought things out and are doing what is right for the situation you are facing and not doing something like rebooting because that is what you always do first.

The first step in the Respond phase is to communicate your intentions. Depending on the type of change you might just tell the rest of your team or you may have to through change control to get approval to do something. The more involved or risky the action you are going to take the more documentation you should have and the more people you should involve to make sure you are not missing anything. Think of it like pool where the shot doesn’t count unless you call it.

Next we make the change. To make the change we follow a written plan that we have hopefully rehearsed. Granted unlocking a user’s account is something you have done 100 times so you can say that is well rehearsed but how many times have you rebuilt the passive node of a production database cluster? Use your best judgment here, erring on the side of being conservative.

A single person should make the change so that the plan can be followed step by step. If something is missing from the plan then it should be added to the plan in case these steps need to be followed again or reversed to roll back the change.

After all that, it is time to go back and start collecting data again. The issue is closed when there are no more symptoms to be addressed and no more fixes to be deployed.

So there you have it, a flexible, scalable methodology for solving just about any problem that any of us might face in the IT world. Use it well.

I like to use the example of the rip current to explain office politics during a system outage. Unfamiliar swimmers caught in rip currents typically make the mistake of swimming against the current trying to get directly back to shore. Many get exhausted before they get back to shore and drown. Swimmers familiar with rip currents will go with the flow, swimming parallel to the shore until they are out of the current then swim back to shore.

Keeping with the rip currents example, what happens when you fight people and say it is not your issue? Do they give up and go away; leaving you to what you were working on or do they fight you harder to prove it is your issue? At times it may even seem like their goal is not prove it is your issue but prove that you are being arrogant and that it could be. Many times it becomes less about the issue at hand and more about winning an argument.

The simple truth is that it is better to go with the flow because it is faster.

An important part of going with the flow is to construct tests that prove that something is our issue. Notice how I say to prove it is our issue rather than prove it is not. The human brain is incredibly open to having tricks played on it, by constructing an affirmative test we trick our brain into trying to find a way to make the test work. We are not happy with just a single failure to recreate the issue, we need to change the test scenario and test more until we can recreate the issue or run out of test scenarios. Most importantly, we are engaged and working to understand what is really going on.

Having a good attitude is key to success in the information technology field. Keeping an open mind is central to that. By going with the flow and looking at issues from the right perspective we can solve problems faster while becoming known for our skill and professionalism.

First and foremost communication prevents your management from being caught by surprise when the VP of Sales calls to ask when they will be able to place orders again.

Communication also prevents duplicated efforts. Many times when a system is down trouble reports come in from everywhere and go to everyone, resulting in a situation where there is no clear problem definition or problem owner. Communicating the problem owner allows the information to flow to a central place, allowing the problem to be properly defined.

Finally, communication allows people to speak up about a recent change. If another team made a change recently they may be able to identify aspects of the issue you are working on that may be related to what they did. This is not saying someone was doing something sneaky although that sometimes happens. Usually this means that something was done and communicated to all the right people but not fully understood by the people it was communicated to or lost in turnover between support rotations. Assume the best here because you need people to speak up sooner rather than later. Treating them badly when they do speak up will only cause trouble in the long run.

So what is the best way to communicate that there is a system issue? It helps to have an email group that includes all IT on-call pagers, management and other key people. If you do not have one I suggest setting one up solely for communicating large issues. It is important not to spam this list, treat it like pulling a fire alarm. It should only be used to communicate system issues from discovery through resolution with updates at regular intervals or large milestones throughout the process.

It is also very important that the distribution list for these emails is IT only. People outside of IT may not know the intricacies of your particular implementation leading to the possibility of spreading misinformation. They are not doing this on purpose, they think they understand and like being involved in something exciting; that they are helping get the word out; doing their part. Let your management craft the organizational communications, they will have to answer for what is said in them.

When sending alert emails keep the subject line general. If you give too much information in the subject line then people can assume it is not their issue and move on. We want to take advantage of that little pit in their stomach that everyone gets when something breaks to get them up to speed on the issue.

Finally, the body of the email should provide a broad overview of the issue including what systems are impacted, any major symptoms including error messages, the number of people impacted and any location specific information. It is very important to keep to the important points here. The body of the email must be short enough to be fully read while long enough to include all important information.

The body of your email should also contain a listing of any resources you need. If you need people then say I will be contacting the primary on-call from network engineering etc. to get their attention. Never use a mass communication to say “I cannot find Mike from the server team. If anyone sees him please tell him I need his help on this issue.” It will make both of you look bad and make the person on the receiving end less likely to help.

Finally, only state the facts when communicating an issue and never assign blame. This is such an important part of the communication. It is important to only state what you know. What you think is not important and who is to blame is even less important. In the end the person that fixes the problem will be asked to explain what went wrong. Chances are they either made the change that led up to the issue or know who did. If there was a hardware failure they will be able to explain it in-depth as well. If you have any doubt about what you are communicating then check with someone that knows more about that particular area.

Once the first alert is sent you generally have 30 minutes to either fix the issue or convene a war room. 30 minutes is a loose rule that I use because if the fix is easy then you will almost always identify the issue, develop a plan and fix it within that time. If 30 minutes goes by and you are still trying to figure out what is wrong then it is time to ask for help. Either way a follow-up alert should go out at the 30 minute mark to update everyone on the issue. The update should follow the same rules as the initial alert although the subject line should be prefixed with “UPDATE: “. Updates should continue at regular intervals until the issue is resolved.

At some point all issues get resolved and that also needs to be communicated. The resolution should include the subject line of the original alert prefixed with “RESOLVED:“. Due to the potential for wide distribution, the alert should never mention anyone by name. The alert should contain a factual description of what the issue was and what was done to solve it, because facts are just facts and cannot convey opinions. Conclusions on the other hand, can convey opinions. Put the facts out there and let people draw their own conclusions. All that really matters is that the people in a position to prevent such a thing in the future recognize what happened and take actions to either prevent or mitigate the impact in the future.

I hope you can see why I think properly communicating issues is important. I have outlined a system that has worked well for me. I strongly believe that anyone handling communications in this manner will be recognized for their professionalism and leadership.

What works for you? Please feel free to leave it in the comments below.

I am honored to close out the final week of the Spring 2011 semester with a topic that I really enjoy: Troubleshooting. I really enjoy solving problems and that has caused me to get pulled into many situations where I could use and develop my troubleshooting skills. I hope to share the things I have learned here this week to speed everyone along in the process, hopefully avoiding some of the pitfalls that I had along the way.

I tried to keep the posts short and easily digestible but I will warn you now that there are one or two that are a bit long. Here is what I have planned for this week:

 
I hope to cover something useful for every level this week. Even as I wrote these posts I was reminded of things I could have done better in recent situations. Look for the “Communication” post tomorrow with “Keeping an Open Mind” on Thursday and “Having a Plan for Every Situation” on Friday. Friday’s post is quite long so it will make for good weekend reading.

The first delivery of the presentation went well, although I definitely have some ideas for how I can improve on it. Look for this deck to evolve a bit over time. The biggest area that I still feel needs work is the narrative around the emergency scenario that I created. Right now it does not tie as well as I would like with the concepts later on in the presentation. Look for the narrative to develop more as I get more opportunities to deliver this presentation.

You can get to the deck from my Presentations page. Yep, that’s right, I have a presentations page now. It feels good to finally have enough content to warrant a dedicated page.

Please have a look and feel free to leave any feedback you might have in the comments section on that page.

The first function I want to show is the one to look up a recipient via Outlook. Despite its limited application this is the more interesting code block to me. I love that I only had to pass a partial name to get back the closest match from the GAL. I chose to include the instantiation of the $outlook object in this function because it made for a more clear example. If I were writing production code I would likely declare $outlook as a global variable then just instantiate it in this function if it had not been already. A great source for more information on calling into Outlook from PowerShell is this article.

The other way to get after this information is via Active Directory. My feeling is that this is going to be the preferred method for most people to look up email addresses. The exception would be those people that want to be able to look up contacts from outside their organization in their local address book. Going the AD route took a few more lines of code, but was still quite easy. The big difference here is that we had to define a search filter by applying the wildcard symbol, “*”, to the portion of the recipient name that had been typed so far. The other big thing to notice is that checks to make sure the results are not null had to be added because unlike the Outlook this search may not return any results. I found this article incredibly helpful in putting this function together.

That does it for the functions. Now for the test harness. This code is not terribly pretty and could probably be made more robust but it seems to work well enough for me to demonstrate the calls. The one thing that I had thought I would need to add is multi-threading to work like AJAX, but since the code runs so much faster than the HTTP calls you would see with AJAX it seems unnecessary. If the lookups run slowly in your environment then it would make sense to add some threading logic. With that, here is the code for the test harness:

I hope you find these functions helpful. Please let me know if you run into any issues with them or know of a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

The rules are simple, just tweet a quote from your favorite holiday movie including the #tsqlHolidayMovieLines hash tag. Holiday movies include any movie that is about the holidays, set in the holidays or even includes holiday decorations (yes, Die Hard is a holiday movie for our purposes).

If you want to play but are not yet on Twitter then go read Brent Ozar’s Simple Twitter Book then go to Twitter.com and sign up for an account.

Lets hear your #tsqlHolidayMovieLines!

Sounds pretty awesome, huh?

The problem is that, as far as I know, this functionality does not exist yet. There might even be very good reasons why it does not exist yet, but this is a feature that I believe is overdue. Because of this, I have opened a Connect item to try to get Certificate Based Application Roles built into SQL Server. Here is the text of my Connect Item:

I am in search of a more perfect application role. What I would like is to be able to sign a Windows executeable or even an individual assembly with a certificate. I would then take that certificate and load it into SQL Server. I would then associate the certificate to Windows users and groups to form an application role.

The benefit of this approach is that depending on the executeable a user is running they could have different rights. A user that has reader on all tables in a database to query with Access might have execute on certain procedures when running the accounting application but have update on other tables when running the payroll software.

If the user changes departments then they would change rights via group membership. If they leave the company then all rights would be removed like any other windows login. All activity would take place under the context of the users login, simplifying auditing.

If you are like me and think this functionality should exist then please go vote up my Connect Item.

I did some digging, opening any of the system views that looked promising. Unfortunately, I was not having much luck. I was about to give up when I remembered that sys.dm_exec_connections will tell what IP address and port. If it had the endpoint_id to join to then I could create my own version of sys.endpoints with the information I need. A quick look at the view showed it had the fields I needed.

Here is the query I came up with:

I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

Now that I am done whining about how difficult it was, let’s talk about the script. Ever had to change the login information for all reports in a particular folder? In my case I was going from SQL authentication to Windows authentication for 4 folders with 15-20 reports each. I had done this before, manually, but I broke a few reports and it took hours to complete. Having to do the whole thing a second time meant a script was in order.

The script below is pretty simple and could easily be turned into a function. It takes a target server, target folder, user name to change to and password to assign. I have the script hard-coded to use Windows Credentials because that is all I have tested with. With that information the script walks through the folder and adjusts the first data source on each report. If you have multiple data sources then you will want to add code to handle that. That is it. (I told you the result is deceptively simple.)

Here is the code:

So that’s it. I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

My first step in getting certified was to figure out what certifications I needed. Since I knew I may someday want to take a shot at the MCM I looked up the required certifications on the MCM page. This is where I got into a bit of trouble. The page I went to listed the required certification as “MCITP: Database Administrator SQL Server” and “MCITP: Database Developer SQL Server”. As this page shows, the 2008 versions of the certifications include 2008 in their names. After some careful reading I decided I should sign up for the SQL 2005 tests.

Last week, while looking over the MCM Readiness Videos, I realized my mistake. On the front page there is a link to the New Path to Microsoft Certified Master: Microsoft SQL Server 2008 and on that page the required certifications are clearly specified as the SQL 2008 versions. From the beginning I had planned to take the upgrade tests to cover the SQL 2008 certifications as well as the SQL 2005 certifications so I will end up where I need to be anyway and get some extra bullet points for my resume.

So there is my cautionary tale. Be careful with the new MCM requirements. You only need the SQL 2008 certifications now. If you have already started down the 2005 path then at least you know you are not alone. I should add that I have been in contact with the right people (not naming here since they may or may not be part of the first line support) to get the incorrect page updated. It may take a couple of weeks to get the incorrect page updated with the coming holiday but I am confident they will get it taken care of.

If you are thinking about the MCM, I say go for it. I can only see them making it more difficult to get once more people start to achieve it.

The trick is to properly manage your VLFs.

What are VLFs? The short anser is that VLF stands for virtual log file. SQL Server database log files are made up of many smaller virtual log files that make it easier for SQL Server to manage the log files. For a much more in-depth answer see the Transaction Log section Paul Randal’s (Blog|Twitter) TechNet article.

The problem with virtual log files is that having too few or too many can slow your server down. I spoke to someone over the weekend that had to wait 30 hours for their database to complete recovery. When they checked they had 1.6 million virtual log files in their database. That is a lot by any standard. A more constant issue with having too many VLFs is that they can also slow down any log operation including logged statements like insert, update and delete. Check out this article by Linchi Shea (Blog) on the performance impact too many VLFs can have. Kimberly L. Tripp (Blog|Twitter) has a great article on how many VLFs you should have.

So if the trick to making your server run faster for free is to manage your VLFs, then how do you go about doing that? Great question. There are lots of great posts out there on how to do this. Here is an article that I wrote detailing the process that I use and some of the reasoning behind it.

If you find any really high counts in your environment, we’ll say greater than 2 million VLFs, please leave a comment with how many. I am curious to see what the highest value is.

After some digging through various VBScript files I was able to confirm that I use a WMI query of Win32_Volume. The query I have is in a highly custom script that does not lend itself to being understandable if taken out of context so I went ahead and converted the logic to PowerShell, then messed with it a bit to come up with something human readable. Here is that script:

Update: Nicholas Cain (Blog|Twitter) suggested using 1GB to get results in gigabytes instead of using /1024/1024/1024. I was curious about this trick and after a little toying around, was able to adjust the script to allow the units of measure to be specified at the top. So go ahead and give it a try.

So there is the code as promised. The next logical step would be to add a call inside the loop to insert this information into a table for trending and alerting.

Please do not run this on an important machine before reviewing it thuroughly. I offer no warranty beyond a sympathetic ear if this script breaks something.

Here are the steps if you want to try this yourself:

1. Open Profiler
2. Specify a server name
3. Use the Blank template
4. Place a check in the box next to User Error Message in the Errors and Warnings group
5. Scroll right and click the column header for TextData
6. Expand Like and type Cannot open database%
7. Click Run

Once you have the trace up and running you just have to wait for the process to try to log in again and you will have plenty of information to go do some finger pointing. If you are like me and like to test your traces to make sure they are working or just want to try this out before you need it in real life, here is a PowerShell script to simulate the failures:

I hope you find this information useful. Please make sure you are comfortable with the process described above before trying it anywhere important. The steps above have worked well for me but your results may vary. I offer no warranty beyond a sympathetic ear should you run into trouble.

This script is written to compress all tables in a database without wasting any time on analysis. I have implemented the script as a stored procedure that takes 2 paramaters. The first is whether to use row or page compression and the second is how long to run for in minutes. The intent would be to call the stored procedure from a job on a regular basis to run for x minutes compressing everything it can then shut down and wait for the next run time and do the same. Eventually all objects in the database will be compressed.

I would recommend against running this without any analysis against any database. That said, it can be a handy tool for compressing an ODS or even an EDW. It would also be a great way to pick up “the rest of the tables” in a database where proper analysis has been done and a particular compression type has been applied to a small set of tables.

Here is the script:

I hope you find this stored procedure useful, but I realize it is not for everyone. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

After doing a quick search on the internet for how to use the column I ended up in Books Online. The definition for the column published there is “Internal only. Is not nullable.” Now that I knew someone did not want me to use the field I really wanted to use it. The field contained a really big number so I started looking at how to translate it into a time. Given that it was a really big number and seemed to be counting down, I tried adding it as milliseconds to the current date. I was a little shocked when the query returned a time a few minutes into the future that ended up pretty close to right on the first try.

Here is the query that I came up with:

As I ran the query throughout the weekend I noticed that it was not always 100% accurate. A big thing I noticed is when restoring a SQL 2005 database to a SQL 2008 server the percent complete will be at or near 100% and the estimated completion time will be the current time while the conversion from SQL 2005 to SQL 2008 happens. That said, I still find this query very useful for giving a ballpark end time to a multi-hour restore and also found it very helpful for gauging the impact of changes like shutting off other jobs on the server or asking people to postpone work that adds x hours to the process.

I hope you find this query useful. I expect that it will work for any process that reports progress so the applications are definitely not limited just to restores. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

UPDATE: Aaron Bertrand has a version of this query that shows more information available here: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/30/when-will-my-backup-restore-index-reorganize-finish.aspx

So what is the DAC? It is a special reserved connection that allows 1 connection from the local machine by appending “ADMIN:” to the beginning of the server or server\instance name. (Exact instructions are available here) The DAC is by default only available to clients connection from the machine that the instance is running on. There is a setting that allows people to connect over the network but in an emergency it is good to know who is using that connection. Forcing people to log onto the box to use the connection means you can see who might have the connection by looking at who is connected to the server. Remember you can’t connect to SQL Server to see who is connected if things are bad enough that the DAC is your only option.

The great part about the Dedicated Administrator Connection (DAC) is that it is always available for someone to connect. Even when normal connections to the server do not work the DAC is ready to accept a connection. Once connected via SSMS (Query Window not Object Explorer) or SQLCMD a DBA can quickly diagnose and resolve an issue.

We no longer have to blindly restart SQL, hoping that we are not setting up a situation where our most important database will be in recovery for minutes or hours.

We now have the tools to accurately diagnose a problem the first time it happens.

We just have to remember to use them.

Since I had identified a real problem I decided it was time to turn to my problem solver, SQL Server Management Studio. I was able to quickly throw together a stored procedure that I can install on each of my machines then add custom alerts to my monitoring tools to call it. Since some tools run at the server level and others at the database level I wrote the script to work at the database level. For simplicity I have included a script at the bottom to run this stored procedure on all databases on a server.

The stored procedure logic is to get all identity columns in a database, using the column type from sys.types to calculate percent full based on current identity value vs. maximum identity value for that type. The inner query then returns those values back to the outer query to be filtered and have severity assigned. I could have written the whole thing as a single select rather than nesting it but writing it this way made it so much more readable.

Here is the code for the stored procedure:

Here is a script to run the stored procedure for all databases on a server:

Please let me know if you run into any issues, have any ideas that would make this stored procedure better or just want to share how you are using it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

First, let me set the scene: One of the applications I support has had issues with deadlocks for some time now. The deadlocks are really a result of design issues that it would be too expensive to fix. One of the challenges of the current design is that some of the data is partitioned the old fashioned way, in multiple tables, based on request type. The challenge of this design is it means there are two tables that have a sequential integer that is shared between them. The idea is to be able to query both tables, union the result sets together in sequence as though they had been in the same table. To accomplish this task a third table was added to track the sequence number as an identity column. A stored procedure inserts a record into the table, grabs the identity value and then deletes the inserted row. The deadlocks have been happening in the stored procedure that grabs that identity value.

Digging into the issue, my first step was to reproduce the deadlock to make sure I had a grasp of what was going on. I will include the code here in case anyone wants to follow along at home.

Step 1 is to create a table:

Step 2 is to insert some data and create some locks. Run this code in the same window:

Step 3 is to create more data and more blocking. Run this code in a separate window:

Step 4 is to create some blocking. Run this in the first window:

Step 5 is to create the deadlock. Run this code in the second window:

During my testing I had a few seconds between setting up the deadlock and the deadlock monitor reacting to it so I was able to pull the “All Transactions” report by right clicking on the database in Management Studio and selecting Reports > Standard Reports > All Transactions.

Here are the results I saw:

Locks before adding an index

I spent a while digging into this, even breaking out DBCC PAGE to look at the page structure to try to figure out what was happening. After working through several wild theories, I looked at the locks again. That’s when it hit me: Why would the first transaction try to grab an update lock on the row inserted by the second transaction? There are no indexes on the table.

To delete a record from a table with no indexes SQL Server has to do a table scan, meaning it has to touch every single row to see if they qualify. To avoid deadlocks SQL Server grabs an update lock before reading the record. In this case the update lock is prevented by the exclusive lock still held by the insert. To see if I was on the right track I looked at the plan for the delete.

Here it is:

Query plan showing table scan while performing a delete

At this point I am pretty sure I am on the right track so it is time for more testing. The next thing I did was to put ROLLBACK WORK commands in both of my query windows to make sure that both transactions were completely out of the way. I then ran the following code:

With the index in place I re-ran steps 2-5 above and everything worked perfectly. Let’s look at why that happened. The first step is to look at the new query plan for the delete after the index create:

Query plan for delete statement with index yielding a seek

The final proof that I have fixed the deadlock by adding an index is to look at the transactions report. If the index fixed the problem the report should show that the update lock has disappeared. Here is the report:

Locks after index was added

The update lock is gone and so is the deadlocking. Adding an index to the table has resolved my deadlock problem. The test that I did here used a clustered index but the same results would be available with a non-clustered index.

I know this was a long post. Thanks for sticking with me all the way to the end.

Buying into the cloud was the easy part. Getting to the point where I can convince people in my organization that they should put their application in the cloud is another story. This is where I could use your help today. I have the easy parts of the argument down. The big thing is time. I can spin up a new Azure database in a couple of hours, while it could take a day or more to get a VM or server spun up depending on how isolated the application needs to be and how busy the respective teams are. Another interesting benefit is that the way the billing is set up it really makes it easy to track return on incubator type projects.

I get the great parts of the cloud but there are still some things I could use help with. Here are the questions I have not had much luck finding answers to or have not dug into yet (I thought of 1 or 2 questions as I was writing this):

How secure is my data?

This is a big one. How can I prove that my data is secure both in the database and over the wire? I have heard anecdotally that data can be encrypted at the application before being sent over the wire and stored in the database but how would this look? What would performance be like?

How do backups work?

Can I back up a database to restore to an internal server or restore a database from a local backup to Azure? Would I still have the opportunity to manage recovery like I do on servers I own?

How does maintenance work?

Can I / do I need to set up the usual maintenance jobs to keep indexes and statistics up to date, check for corruption, etc.?

What is support like?

This is sort of a double question but how do I monitor the performance of my database in the cloud? Are there any tools available that make it easier to proactively monitor for issues? What kind of support can I expect if I do have issues? Is there someone I can call? Is there someone monitoring that will call me? Am I expected to be able handle things on my own?

What about DR?

In the Azure demo I saw I distinctly remember the presenter referring to a Chicago data center. Is that a single data center located near Chicago or a group of them that share the load? Would I need to plan for the possibility that natural disaster or a fiber seeking backhoe could knock my database offline? Would my database move to another data center somewhere else in the world and just run a little bit slower until my chosen data center is back online?

So there you have it. Those are my concerns about moving data to the cloud. Overall, I think the cloud will be huge, although I do not know enough about it to know if I am ready to add it as a tool in my toolkit yet. If you know the answer to any of my questions please feel free to leave them in the comments below. Thanks for taking the time to hear me out and share any knowledge you may have.