While helping unknown faces via MSDN SQL Server Forum, I have asked many times to share the “SQL Server ErrorLog”. Most of the time, I need to explain where the ErrorLog(s) are located. so this post is to help those who are new to SQL Server. Well, if you ever call Microsoft SQL Support for any problem with SQL, they (including me) would ask SQL ErrorLog.

You may ask “What is ErrorLog file?”
SQL Server maintains its own error logs that contain messages describing informational and error events. I personally don’t like name of the file “ErrorLog” as this file does not contains only errors, it also contains information messages. This is the reason we have added additional text in information message called “This is an informational message only. No user action is required”.

• Location of Errorlog when SQL Server is running and you are able to connect:
  • Connect to SQL Server using SQL Server Management Studio by providing correct name. To find the name to connect, refer my earlier post
  • In Query window run below command
    sp_readerrorlog
  • Here is the output

  

  • Highlighted area “Logging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Log\ERRORLOG'” is current ERRORLOG file 
  • If you look at folder D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Log\ here are files.

          As you can see above that LOG folder contains many files. I have provided little information about them in picture.

• Location of Errorlog when SQL Server is running and you are NOT able to connect:
  
  There could be situations where you are not able to connect to SQL Server because SQL Server service is not getting started. In those situations, sp_readerrorlog can’t be run. Now, we have to find ERRORLOG file using other technique. Here we go.
  • Open SQL Server Configuration Manager:
    Go to Start > All Programs > Microsoft SQL Server 2005 (or 2008) (or 2008 R2) > Configuration Tools > SQL Server Configuration Manager
    
  • Once you open Configuration Manager, you would get below screen. There you need to locate your SQL Server Instance, right click and properties.
    
    
    
    
    
  • Here is the Properties window. Come to Advanced tab & Click on drop down for “Startup Parameters” value and you would see three parameters (can be more also). As shown below there would be –d, –e and –l parameters. Value after –e is your SQL Server Error Log location.
    
    
    

Now if someone is asking ERRORLOG file, please provide using above two methods. You can also get it from SQL Server Management Studio, as described here 

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
www.SQLServer-Help.com
Twitter @blakhani