Real Life DBA: finding errors in SQL server

Posted in: Microsoft SQL Server, Technical Track

I tend to spend time on a few different forums at times, mostly the StackExchange network, and find folks asking questions about various errors seen in the error log of an SQL Server instance. Most DBAs are aware that errors that SQL Server provides can be vague at times, so it takes a bit of knowing where to look for more information. In some situations it is after the fact before you can get to the instance to start your analysis so you have to focus on those areas that contain historical information. A few of those places to check are:

  1. Various DMVs, depending on the error, but things like connectivity errors you can search the DMV sys.dm_os_ring_buffers.
  2. System_Health XEvent session has various things like deadlock reports and some of the things you find in the ring buffer are written to this session as well.
  3. Default Trace, this has been around since 2005 but being that Profiler and Traces are on the depreciation list it is a unknown how long it will stay around, but it can help while it is there.

In the field, we have our Avail Monitoring that customers can utilize for monitoring an instance. It will monitor the error log for a SQL Server instance, and we will get a page when something is found that it does not know about. This is an error message I actually received today for a client:

Error9100_avail_page

That error messages only tells me the SPID that it occurred under, which we know someone else could be using that SPID by the time I get to the server. It does not provide the login or even database it pertains to. So, I went searching around and decided to go check the default trace, and actually found this bit of information:

Error9100_1

Error9100_2

You can see from this the SPID matches and I was able to get the login that caused the error and what was being done. When you get an issue with SQL Server that is vague or does not provide enough information, go back to the basics and recall what options you have in front of you.

 

Discover more about our expertise in SQL Server.

email

Author

Interested in working with Shawn? Schedule a tech call.

About the Author

Microsoft Cloud and Datacenter Management MVP, Shawn has a knack for automating mundane task where IT staff can focus on more business critical issues and task. He has been recognized for his skills in PowerShell and has a broad knowledge of technology around Microsoft's Data Platform and various Cloud providers.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *