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
1. Using TSQL
–find sql server error log location
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
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.
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:
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”
To change SQL Agent ERRORLOG, just run this:
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'<New_Errorlog_Path>\SQLAGENT.OUT’
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.