CONTACT US

How to check SQL Server ERRORLOG location

  • No Comments

4 Ways to Check SQL Server ERRORLOG Location

You can check sql error log path in a few ways:

1. Use TSQL

2. Use SQL Configuration Manager

3. Use EventViewer

4. Registry

1. Using TSQL

Find the sql server errorlog location setting:

  1. USE master
  2. GO
  3. EXEC sys.xp_readerrorlog
  4. 0, --Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
  5. 1, --Log file type: 1 or NULL = error log, 2 = SQL Agent log
  6. N'Logging SQL Server messages in file', --Search string 1: String you want to find
  7. NULL, --Search string 2: String two you want to search for to further refine the results
  8. NULL, --Search from start time
  9. NULL, --Search to end time
  10. N'asc' --Sort order for results: N'asc' = ascending, N'desc' = descending

The result should be:

get sql ERRORLOG location using TSQL
Figure 1 – Get sql ERRORLOG location using TSQL.

2. SQL Configuration Manager

Accessing SQL Server Configuration Manager

  1. Go to “SQL Server Services”;
  2. Click on SQL instance on the right.  (In my case its “SQL2017”);
  3. Right-click, go to Properties.
  4. Look for “Startup Parameters” tab.

One of “Existing parameters” shows the ERRORLOG path.

SQL Server ERRORLOG default location config
Figure 2- SQL Server ERRORLOG default location config.

Here are SQL Server logs location on my laptop:

SQL Server ERRORLOG location SQL Server 2017
Figure 3 – SQL Server ERRORLOG location SQL Server 2017 (PATH).

3. Using EventViewer

Start the EventViewer (you can use the search box near the Start button).

  1. Click on the Windows Logs > Application > Filter Current Log.
  2. Replace <All Event IDs> with EventID 17111 and hit OK.
Microsoft Windows Event viewer ERRORLOG file path (Location).
Figure 4- Microsoft Windows Event viewer ERRORLOG file path (Location).
Adding filter to current log (Event Viewer).
Figure 5 – Adding filter to current log (Event Viewer).
Steps to filter Event viewer log.
Figure 6 – Steps to filter Event viewer log.

4. Using Registry

Are you serious? Are you going to try to look up the path using the registry? Yeah, it is possible.
You have three methods above to do this. I do not see a good reason to go the registry route, but in case you must, here it is:

SQL ERRORLOG - find SQL ERRORLOG - find
Figure 7 – Finding the SQL ERRORLOG using registry.

To change or move the ERROLOG path, I do this using SQL Configuration Manager, change parameter “-e”. Restart the SQL Service.

Warning: if directory does not exist or SQL Server Service does not have enough permissions, SQL Engine will not start.
You will most likely get “The request failed or the service did not respond in a timely fashion….” error.

To check SQLAgent ERRORLOG location, go to SQL Server Management Studio > SQL Server Agent > right-click “Properties”.

SQLAgent ERRORLOG check path
Figure 8 – SQLAgent ERRORLOG check path.

To change SQL Agent ERRORLOG, just run this:

  1. USE msdb
  2. GO
  3. EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'&lt;New_Errorlog_Path&gt;\SQLAGENT.OUT'
  4. GO

When reviewing SQL Server I’ve have never seen when doing SQL Server Consulting, checking Microsoft SQL server Errorlog is one of the first items I do.
It often has a lot of good info.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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