-3

I've had a varied career, but I have never found relevant information in the SQL Server Error Log. Under what circumstances should I consider inspecting it?

0

4 Answers 4

7

I've had a varied career, but I have never found relevant information in the SQL Server Error Log.

I'm happy you've had an easy time with SQL Server :)

Under what circumstances should I consider inspecting it?

An extremely abbreviated list would be:

  • Every day, because who knows what is happening.
  • When any sort of issue arises that is more than superficial query issues.
  • When you want to learn how things work.
  • When you're hunting for new features/functionality/changes.

I've, quite literally, troubleshot the worst SQL Server issues and without the errorlog you're flying blind. In a large majority of cases it is just impossible to troubleshoot without it.

This isn't to say the errorlog is all you need. The basic core items that should always be captured or checked if there are issues or for general health and welfare of the instance are the errorlog, Windows event log for system and application, and the cluster log if using clustering.

2
  • Well darn. It looks like I should be checking that thing more often. Usually, it's just flooded with info telling me that my log backups are working great. Hopefully dbatools can rescue me from that.
    – J. Mini
    Jan 30 at 21:53
  • 1
    @J.Mini It'd be nice if there was a database or server level option for that, but instead we have TF3226 which may help you. Jan 30 at 21:54
5

Well darn. It looks like I should be checking that thing more often. Usually, it's just flooded with info telling me that my log backups are working great.

If you’d like a free tool that narrows the scope of error log results to ones that aren’t useless, you may want to try my sp_LogHunter.

The idea behind it is to only return error log entries of critical importance without all the fluff and nonsense.

3

Here's three more cases, in addition to Sean's list:

  1. When the instance won't start properly (as vonPryz noted).
  2. In similar vein, when a database won't come back online.
  3. When a user is encountering login failures. For security reasons, only a generic error message is shown to the end user. The troubleshooting details that describe the actual problem are stored in the SQL Error Log.
2

A few small additions to the good answers from Sean Gallardy and J.D.:

  1. I've used the SQL Error Log when troubleshooting problems with encrypted connections. In the first ten or twenty seconds after starting up, SQL will tell you about the certificate it is using. For example:

The certificate [Cert Hash(sha1) "B91B739DBF6780EA40F45C8ED9D8F0D2AEBC1EDD"] was successfully loaded for encryption.

Or if it is using a self-signed certificate:

A self-generated certificate was successfully loaded for encryption.

And if it fails to load the designated certificate, it'll look like this:

TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL. Check settings in Configuration Manager. The data is invalid.

  1. It can be a handy way to confirm what port SQL is listening on. You'll see something like this in the first ten or twenty seconds:

Server is listening on [ 'any' 50481].

  1. It can tell you if SPN registration is successful or not:

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/myserver.domain.local:50481 ] for the SQL Server service.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.