on August 11, 2009
Update from Kendra (Nov 2018): I’m keeping this post for posterity, but instead of sp_who2, check out @AdamMachanic ‘s sp_WhoIsActive, if you need a free tool to see what’s running in SQL Server.
I thought I would do a series of posts thinking through general troubleshooting steps I use for a wide variety of issues– everything from slow running performance, system crashes, or application failures where the source hasn’t been identified.
Typically, the first thing I do in almost any situation I can think of is verify if I can connect to the SQL Server instance and verify if I can run sp_who2 and view the contents of the SQL Server Log.
This is actually three separate tests, but you get quite a lot of information about the situation very quickly:
- Can I connect to the instance over TCP/IP? And does it work with the default timeout, or do I have to extend the value to be able to connect? Do I need to specify the port?
- If I can connect, does sp_who2 return results? If so, I do a quick glance through it just to get a quick overview of what’s active at the moment.
- Can I open the SQL Server Log, and if I can, are there any obvious recent events that are unusual, such as deadlocks, timeouts related to accessing disk, or other errors?
So my first pass is to hit the sql server interface in these three ways.
Following this, I tend to either look more into SQL Server, or into OS level information, depending on the situation. More on that to come.