Hello, my name is Curt. I'm a senior principal engineer working with the GSE team.
This video is dedicated to discussing the SQL Server error log. Where it's located, what information it contains and why it can aid us in troubleshooting Microsoft SQL Server issues.
So the quickest way we can normally access the log is going through Microsoft SQL Server Management Studio.
If we drill down to the ‘Management’ container and then down to ‘SQL Server logs’ we see the available error logs that SQL server has collected for this installation of SQL.
There are six archive logs that are held by SQL of past events that have occurred to this to the server. The current log is the one that collects all the information since the last restart of SQL Server, and the error log is regenerated every time SQL server is restarted.
So in looking at the current log to give you an idea of some of the things that we can expect to see here. First item that's that we see is the type of installation for SQL Server, in this case it's SQL Server 2019.
It also gives us product version information. In this case we're working with a base RTM installation of SQL Server. We also get OS information.
In this case we're dealing with a Workstation installation of Windows 10. Moving through the log and what you'll see in Studio Management is the earliest events are recorded at the bottom of the log as it appears, and the latest events are near the top of the log.
Working through, or looking through the log we see things like the authentication mode which is mixed, we see the actual location of the error log - this is the default location for the error log program files/Microsoft SQL server and the installation folder for the instance of SQL itself.
We also see the information for the service account that was used to start SQL Server. Another entry that can be important are the startup parameters, the default location that SQL currently has for the database files, also the location of the error log once again, and also the log files that we see for SQL Server.
We also get information about the cores on the system, how many available processors SQL can use and also the amount of available memory that SQL is seeing at the physical level when the system is booted up.
Other information that follows is the system database is coming online, if we have user production databases we see that information as well, but the most notable thing about the SQL server error log and its greatest benefit is actually returning information about errors that are seen by SQL server.
And this can not only be a startup time but throughout the lifetime of the service running. So we have an error here that's pretty inconsequential but it's making a reference to Polybase, the Polybase configuration on the system.
Actually Polybase wasn’t installed with this, there is no Polybase configuration information, so this is once again a non-issue for this installation, but this is an example of a message that we see as an error.
Now to get into a little bit more detail about where the error log can be extremely beneficial is for instance when SQL Server isn't starting.
So this particular log is the previous log that I had to our current log, and SQL Server in this startup attempt actually didn't start.
And this is a manufactured error, and the way I manufactured this error was I moved the location of the model database, which is a system database.
It's a template database for all databases created by SQL Server. When SQL Server starts it has to start up and use a tempdb database, and the model database is the template for the tempdb.
So without a model database tempdb could not start, or could not come online and the SQL Server service could not start.
This is an example of what we would see in the error log. We would see the error, we would see more information about why the error was generated - in this case it couldn't find a database file the model database file - and later on it'll tell us that it couldn't find the model database log file which means the tempdb database could not be created.
So this is the bad news that the error log can return to you to give you information about how to troubleshoot your issue.
Now, we need to keep in mind that when we see an entry for an error like this this is also going to be included in the application logs.
So we see the very same event being recorded in the system application log, and the first error message we see is telling us that the database file wasn't found, and the second one tells us the log file wasn't found.
So once again a manufactured example of a failure, of course this can be very critical to you if you're troubleshooting a real life issue.
For instance if a major database file is corrupted, like the model database, the service for SQL will not start and this is the area that you're going to get information to learn about that and to troubleshoot it.
So enough about the contents of the error log. We can also access the error logs live in file explorer. And once again we need to know the file location of the error log, we saw that in the error log itself.
We know this is the, that it's located in the default location for the error log and it's in the log directory for that installation of SQL Server.
See the same information always – we’re given the choice of the text editor you want to bring it up with. Once again the view is different.
Here the earliest events are at the top of the log when you look at the raw log itself, and the latest are at the bottom.
There is one other way to find the error log location, and that is going to the program installation off the Start menu for that instance of SQL Server.
When we go in here we go to ‘SQL Server Configuration Manager’. Configuration manager shows all the services for this installation of SQL.
If we go to ‘SQL Server Service’ right click on that, go to ‘Properties’, ‘Startup Parameters’, the ‘-e’ parameter is where the error log is located.
And sometimes it's really important to understand this because you may not have an installation of Studio Management to look at.
If you don't then you have to start thinking about all the possible drive locations that are available for that particular installation of SQL.
Many times you can simply cut to the chase, go into Configuration Manager, find those startup parameters, and when you do that you can find out exactly where that error log is located.
All right that concludes our look at the SQL Server Error Log. I hope this video helped you understand where to find the log, and how it can help you in troubleshooting Microsoft SQL Server.
Thank you for watching, and goodbye.
