How do you change 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 sql server error log location

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

get sql ERRORLOG location using TSQL

2. SQL Configuration Manager

SQL Configuration Manager > “SQL Server Services” > click on click on SQL instance on the right. In my case its “SQL2017” > right click > Properties > “Startup Parameters”.

One of “Existing parameters” shows ERRORLOG path.

SQL Server ERRORLOG default location config

 

Here is sql server logs location on my laptop: 

 

3. Using EventViewer

Start EventViewer > click Windows Logs > Application > Filter Current Log > replace <All Event IDs> with EventID 17111 > hit OK

4. Using Registry

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

SQL ERRORLOG - find path - using registry

 

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

Warning: if directory does not exist or SQL Server Service does not have enough permissions, SQL Engine won’t start and 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

 

To change SQL Agent ERRORLOG, just run this:

USE msdb 
GO 
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'<New_Errorlog_Path>\SQLAGENT.OUT’ 
GO

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

 

Leave a Comment