Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooing Skills

Help : Where is SQL Server ErrorLog?

Posted by blakhani on June 26, 2011


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

    ERRORLOG

    • 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.

                          LOG Folder

          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
      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.

      Configuration Manager

    • 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.

      SSCM Properties

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
Twitter @blakhani

About these ads

27 Responses to “Help : Where is SQL Server ErrorLog?”

  1. http://www.sqlservercentral.com/blogs/joewebb/archive/2010/10/19/where-is-the-sql-server-errorlog-file_3F00_.aspx

  2. Nice and clear information. Could you please have an article about how to find deadlocks and using ErrorLog as well.

  3. Gaurav(GB) said

    Add 1 more method which I have seen many customers are usually struggling. How to see it if SQL server is down and due to WMI issues, the Configuration manager could not be opened, i.e. How to see it from registry.

  4. blakhani said

    Hi Gaurav,
    Are you asking registry keys to find the error log location?

  5. Chirag Shah said

    Balmukund, Good information.

  6. [...] Once you open Configuration Manager, you would get below screen. There you need to locate your SQL Server Instance, right click and properties. [...]

  7. MAFRI said

    mafri…

    [...]Help : Where is SQL Server ErrorLog? « Help: SQL Server[...]…

  8. [...] the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.” TITLE: Connect to [...]

  9. [...] http://sqlserver-help.com/2011/06/26/help-where-is-sql-server-errorlog/ [...]

  10. […] sensitive. Error Log would show below information. How to get error log location? I have already blogged about […]

  11. […] Here is a good screenshot about the rest of SQL Server error. […]

  12. […] http://sqlserver-help.com/2011/06/26/help-where-is-sql-server-errorlog/ […]

  13. […] check errorlog and here is what we would […]

  14. […] Server Errorlog would have different message as […]

  15. […] For those who don’t know what/where is SQL Server ERRORLOG, please read this […]

  16. […] the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user […]

  17. […] Server Errorlog would have different message as […]

  18. […] Server ERRORLOG also shows this […]

  19. […] I checked ERRORLOG, I found […]

  20. Roger said

    Un post vraiment rempli de bons conseils

  21. […] have always suggested them to start looking at errorlog and check what is the error which most of the DBA have […]

  22. Pretty nice post. I just stumbled upon your blog and wished to say that I’ve truly enjoyed surfing around your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!

  23. […] very first thing which I always ask is look at ERRORLOG and find error at exact same time. By default, auditing of failed logins is enabled. In this case, […]

  24. […] doing some testing with TempDB database I started getting below errors in ERRORLOG and SQL Server was not getting […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 788 other followers

%d bloggers like this: