How do I look at my error log if SQL won’t open?

Kenneth Fisher — Mon, 22 Oct 2018 13:00:04 +0000

Quick and easy post today. Hopefully you’ve opened the error log on a SQL instance. However, what happens if you don’t have the log viewer in SSMS? In fact, the instance won’t come up at all so you really need to see what went wrong.

Fortunately, the error logs in SQL are just text files, even though they don’t actually have that extension. The trick is knowing where they are.

If you need to you can generally look up the location in the startup parameters in SQL Server Configuration Manager.

Parameter -e

-eC:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CS\MSSQL\Log\ERRORLOG

This means that the log files will be in the log directory with the pattern ERRORLOG.[number]. Now, this is the directory for my log files. Yours should follow this pattern:

-e[InstallDrive]:\Program Files\Microsoft SQL Server\MSSQL[VersionNumber].[InstanceName]\MSSQL\Log\ERRORLOG

The current log file is ERRORLOG with the most recent one ERRORLOG.1. For each older one add 1 to the file extension. Note, that means that when you recycle the log (start a new log file) every file gets renamed, and the oldest one is deleted.

Anyway, once you’ve navigated to the path you can open any of the error log files up with notepad and easily read them. The most recent information will be at the bottom of the file.

SQL Homework – December 2017 – View the log

Kenneth Fisher — Mon, 04 Dec 2017 14:00:21 +0000

Last homework of the year. Last month was to create a view. This month it’s time to take a look in the log! No, not the transaction log. The SQL Server Log. Where error events, backup events, startup events etc, etc get stored. If something goes wrong on your instance, or someone is having a hard time connecting to it this is one of the first places you should be looking at. Given the importance of the subject, for the first time, I’m putting two sets of work. One for Jr/Mid level individuals and one for more Sr. level individuals.

Jr & Mid level people

Open the log in the GUI. 50 points
Find the last time your instance was restarted and review the entries. 15 points
See what other types of entries there are. 10 points
Figure out how to change the settings to write both successful and unsuccessful logins to the log, just one or the other, or neither. 15 points
Figure out how to modify the number of log files kept. 10 points

Sr level people

View the log files using a text editor. (Not in the SQL GUI.) 10 points
Find or write a script that will search the error log programmatically. 40 points
Figure out how to turn off the backup entries. 20 points
Figure out how to write to the log. 10 points
Do you have a way to automatically check your log for issues? Should you? If you don’t, develop a plan to do so. 20 points and you are less likely to have a resume generating event.

SQL Server Logs – SQL Studies

How do I look at my error log if SQL won’t open?

SQL Homework – December 2017 – View the log