T- SQL Tuesday #187 Round Up: Troubleshooting Weird Problems
T- SQL Tuesday #187 Round Up: Troubleshooting Weird Problems
I was excited to host my first T-SQL Tuesday to find out how you solve problems. It’s a big topic, and I imagine it’s difficult to fully put to paper how your brain works, but I am thankful for the folks who did. Here’s my T-SQL Tuesday Round Up on troubleshooting weird problems.
Why “Turn It Off and Turn It On Again” Still Works – Except for Databases!
Rob Douglas (LinkedIn) kicked it off from New Zealand by explaining why turning it off and on again was never great advice in the first place, and is generally pretty bad advice for database systems. He shared how one client had strange things happening, jobs that were randomly cancelled, and found there was actually a scheduled script to restart SQL Server because of its memory usage! It’s shocking how common it is for sysadmins to become worried about a database server operating with high memory utilization. As technology consumers, though, we are so used to hearing it — I even have to occasionally reboot my car’s infotainment — that having it be part of a process for the database doesn’t seem out of line, even when it can have a ripple effect that lasts for hours afterward.
Breaking Down Those “Weird” Problems
Another Rob, of the Farley family (LinkedIn) writes from Australia about what to do when you encounter a unique situation you don’t recognize. As a fellow consultant, Rob has also seen a lot of weird things and enjoys mentoring and guiding people through solutions, but sometimes even he is initially stumped. His solution is breaking things down, which includes a process of questioning some of those things you think you’re sure of. Clauses like SUM or HAVING can seem obvious on the surface but have nuances that may need further research to fully understand. I applaud people who have the humility to acknowledge being wrong and re-examine previously held convictions.
Sometimes the Fix is To Delete the Problem
Chad Callihan (LinkedIn) chimed in from Ohio to remind us that asking questions is important, because sometimes you can just delete the problem because it’s part of an old/redundant feature that no one needs anyway. Questions like, “Does anyone know what this process does?” or “Is this still needed?” are great ones to ask when you have folks around who’ve got history with the application. Things can get a little tougher when those folks are no longer around, but with some caution, testing and proper change control (which includes code versioning), you should be able to safely remove old processes. I wish more of my problems could just be deleted!
Problems Have Layers
Finally, Rietse Eskens (LinkedIn) blogged from the Netherlands about how problems typically have many layers. His approach is to peel them back and try to filter for the ones which context tells him are the likely culprits. I love this approach and have used it many times in my own troubleshooting, often mimicking the refrain from the image in my invitation post. One of my personal favorite areas where this technique is useful is in reading cluster logs. There is a massive amount of information in there, so the ability to use context and filter things out is invaluable.
Ask Those Crazy Questions
This past week was a bit of a blur for me. I accompanied my youngest child to Kent State for orientation, which took two full days. It was an incredible experience but it cut into my work time which means it also cut into my blogging time. So instead of a full post I’ll leave you with my own personal tip:
Don’t be afraid to ask questions that sound crazy. I once helped a client troubleshoot a problem where data was being updated twice by a SQL Agent job, but the history only showed it executing once. This happened several days in a row and was causing the client a considerable amount of rework to undo the second update. After searching around the environment, I had to get on a call and ask, “Is it possible you have another server on your network with the same name that’s also running this process?”
It sounds crazy and impossible, and it can be dangerous to ask this kind of question because it can imply incompetence (either my own or the client’s sysadmins). But it was the only explanation I had, and it turned out to be correct. An old version of the server had been decommissioned and turned off, but someone mistakenly turned it on again because of a report looking for unpatched servers. Because of the network configuration, instead of updating itself, it was connecting to the new production server and performing the update a second time. Turning off the server (and disabling the SQL Server service) kept it from recurring.
I have found that if you prefix a question like this with, “I know this might sound crazy, but I have seen so much crazy stuff in this line of work that I just have to ask….” that it can be disarming enough that people may actually stop and think about it before assuming you are insulting them.
Thanks to everyone who contributed and gave me more ideas for problem solving! I look forward to the next T-SQL Tuesday!
