SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

Whenever someone reports some weird error on my blog comments or sends email to know about it, I always ask to share SQL Server ERRORLOG file. There have been many occasions where I need to guide them to find location of ERRORLOG file generated by SQL Server. Most DBA’s are intelligent and know some of these, but this is my try to share my learning about ERRORLOG location.

I decided to write this blog so that I can reuse it rather than sending steps every time. At this point I must point out that even if the name says ERRORLOG, it contains not only the errors but information message also. Here are various ways to find the SQL Server ErrorLog location.

A) If SQL Server is running and we are able to connect to SQL Server then we can do various things. So we can connect to SQL Server and run xp_readerrorlog.

USE MASTER
GO
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'
GO

SQL SERVER - Where is ERRORLOG? Various Ways to Find ERRORLOG Location errorlog-01

If you can’t remember above command just run xp_readerrorlog and find the line which says “Logging SQL Server messages”.

B) If we are not able to connect to SQL Server then we should SQL Server Configuration Manageuse. We need to find startup parameter starting with -e. Below is the place in SQL Server Configuration Manager (SQL 2012 onwards) where we can see them.

SQL SERVER - Where is ERRORLOG? Various Ways to Find ERRORLOG Location errorlog-02

C) If you don’t want to use both ways, then here is the little unknown secret. The ERRORLOG is one of startup parameters and its values are stored in registry key and here is the key in my server. SQLArg1 shows parameter starting with -e parameters which point to Errorlog file.

SQL SERVER - Where is ERRORLOG? Various Ways to Find ERRORLOG Location errorlog-03

Here is the key which I highlighted in the image: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters\

Note that “MSSQL12.SQL2014” would vary based on SQL Server Version and instance name which is installed. Here is the quick table with version reference

SQL Server VersionKey Name
SQL Server 2008MSSQL10
SQL Server 2008 R2MSSQL10_50
SQL Server 2012MSSQL11
SQL Server 2014MSSQL12

In SQL Server 2005, we would see a key name in the format of MSSQL.n (like MSSQL.1) the number n would vary based on instance ID.

Here is a key where we can get mapping of Instance ID and directory.

SQL SERVER - Where is ERRORLOG? Various Ways to Find ERRORLOG Location errorlog-04

In the above image, you can see that this computer has a default instance (Instance Name MSSQLSERVER) of SQL Server 2012 and named instance (Instance Name SQL2014) of SQL Server 2014.

In case you are contacting me for any error, get the Errorlog location using this blog.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
Interview Question of the Week #012 – Steps to Restore Bak File to Database
Next Post
SQL SERVER – Knowing Nested Transactions Behavior with SQL Server

Related Posts

10 Comments. Leave new

  • Very useful tip

    Reply
  • What is the use to find errorlog location ?

    Reply
    • Piya – Whenever there is a need to know about some critical error or warnings that SQL Server encounters, we will need to read the errorlogs. Most of the times it is in the default location, but from time to time when a new DBA joins a team, they need to make sure the Errorlogs are placed in locations as prescribed by company standards. So these methods are ideal in such situations. I am sure you would have seen a number of blog wherein I rely on the error messages or warnings put on the Errorlog files.

      Reply
      • i am unable to connect to sql server through visual studio. in sql server every is working fine.

        the log file contents are
        2015-06-29 16:47:54 – ? [393] Waiting for SQL Server to recover databases…
        2015-06-29 16:47:56 – ! [298] SQLServer Error: 15247, User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)
        2015-06-29 16:47:56 – ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object ‘sp_sqlagent_has_server_access’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
        2015-06-29 16:47:56 – ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object ‘sp_sqlagent_get_startup_info’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000]
        2015-06-29 16:47:56 – ! [298] SQLServer Error: 229, The UPDATE permission was denied on the object ‘sysalerts’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000] (DumpAndCheckServerVersion)
        2015-06-29 16:47:57 – ! [298] SQLServer Error: 229, The INSERT permission was denied on the object ‘syssessions’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000]
        2015-06-29 16:47:57 – ! [000] Error creating a new session
        2015-06-29 16:47:57 – ? [098] SQLServerAgent terminated (normally)

      • What you are seeing is SQLAgent log file.

  • we can use xp_readerrorlog
    and observer first few lines of output there we can also get the errorlog location

    Reply
  • Thank you Pinal, very helpful notes

    Reply
  • ashishrohitce@gmail.com
    June 30, 2017 1:57 pm

    I change windows server password now sql server 2008 r2 not working (service not start)

    Reply

Leave a Reply

Menu