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
- 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
Twitter @blakhani
Help: SQL Server 
Nitin Chandra Salgar said
http://www.sqlservercentral.com/blogs/joewebb/archive/2010/10/19/where-is-the-sql-server-errorlog-file_3F00_.aspx
Arbi Baghdanian said
Nice and clear information. Could you please have an article about how to find deadlocks and using ErrorLog as well.
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.
blakhani said
Are you talking about location of error log using registry keys?
blakhani said
Hi Gaurav,
Are you asking registry keys to find the error log location?
Chirag Shah said
Balmukund, Good information.
blakhani said
Thanks Chirag.
Help : Where is SQL Server ErrorLog? - Balmukund Lakhani's Blog said
[…] Once you open Configuration Manager, you would get below screen. There you need to locate your SQL Server Instance, right click and properties. […]
MAFRI said
mafri…
[…]Help : Where is SQL Server ErrorLog? « Help: SQL Server[…]…
Help : I lost sa password and no one has System Administrator (SysAdmin) permission. What should I do? « Help: SQL Server said
[…] 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 […]
How to find the ErrorLog path in SQL Server 2012 - vyella - Site Home - MSDN Blogs said
[…] https://sqlserver-help.com/2011/06/26/help-where-is-sql-server-errorlog/ […]
SQL SERVER – The Story of a Lesser Known Startup Parameter in SQL Server – Guest Post by Balmukund Lakhani | Journey to SQL Authority with Pinal Dave said
[…] sensitive. Error Log would show below information. How to get error log location? I have already blogged about […]
3 SQL Server ERRORLOG must have configurations + 7 useful techniques | SQLHouse.com said
[…] Here is a good screenshot about the rest of SQL Server error. […]
How to find the ErrorLog path in SQL Server 2012 - SQL Server and related stuff - Site Home - MSDN Blogs said
[…] https://sqlserver-help.com/2011/06/26/help-where-is-sql-server-errorlog/ […]
A-Z of In-Memory OLTP : Troubleshooting database Startup and recovery failures « Help: SQL Server said
[…] check errorlog and here is what we would […]
Question: What is the difference between disabled Login and Deny Connect permission? « Help: SQL Server said
[…] Server Errorlog would have different message as […]
SSMS Tip: Merging ERRORLOGs from different machines using Log File Viewer « Help: SQL Server said
[…] For those who don’t know what/where is SQL Server ERRORLOG, please read this […]
To Be….or not to Be….a sysadmin | What's Weird in SQL this week said
[…] 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 […]
Difference between disabled Login and Deny Connect permission | MSSQL Server Admin Forums - Raju Rasagounder said
[…] Server Errorlog would have different message as […]
Myths of SQL Server: Rollback Service Pack with Resource Database? (MSSQLSystemResource) « Help: SQL Server said
[…] Server ERRORLOG also shows this […]
SQL Internals – How to map login SID to domain account? « Help: SQL Server said
[…] I checked ERRORLOG, I found […]
Roger said
Un post vraiment rempli de bons conseils
Solution : The connection to the primary replica is not active. The command cannot be processed. « Help: SQL Server said
[…] have always suggested them to start looking at errorlog and check what is the error which most of the DBA have […]
Beryl Eugene said
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!
blakhani said
Thanks Beryl! Appreciate it.
Help : How to find cause of “Login failed for user” error « Help: SQL Server said
[…] 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, […]
Tips and Tricks : Error: 5171 – tempdb.mdf is not a primary database file « Help: SQL Server said
[…] doing some testing with TempDB database I started getting below errors in ERRORLOG and SQL Server was not getting […]
Tips and Tricks : Why TempDB files lesser than Configured? « Help: SQL Server said
[…] I looked into the SQL Server ERRORLOG and found below messages during […]
SQL SERVER – FIX – The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run | Journey to SQL Authority with Pinal Dave said
[…] Whenever I have any weird problems with SQL, I always look at ERRORLOG files for that instance. If you don’t know the location of Errorlog, you should refer Balmukund’s blog (Help : Where is SQL Server ErrorLog?) […]
Explanation : Error: 5125, Severity: 24, State: 2 « Help: SQL Server said
[…] log shipping and backup restore, one of my database gone bad and was not coming online. I checked ERRORLOG and found below […]
SQL SERVER – FIX – Error: 905, Severity: 21, State: 1 – Database ‘xxx’ cannot be started in this edition of SQL Server because it contains a partition function ‘xxx’. | Journey to SQL Authority with Pinal Dave said
[…] Other ways to get Errorlog are explained in detail by Balmukund (b|t) on his blog (Help : Where is SQL Server ErrorLog?) […]
SQL SERVER – Database Stuck in “In Recovery” Mode After Restart | Journey to SQL Authority with Pinal Dave said
[…] very first thing I always check is ERRORLOG. In Errorlog, we should see the very first message in the database (TestMe is the name of the […]
Solution – SQL Server Backup Failing with EXCEPTION_ACCESS_VIOLATION « Help: SQL Server said
[…] we open ERRORLOG we saw […]
nefink said
Are runtime exceptions, such as constraint violations, posted to the ERRORLOG?
blakhani said
nope.
Server Error Log — netherlandsminecraft said
[…] https://sqlserver-help.com/2011/06/26/help-whe… →Jun 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, … […]
urshiva said
Nice post.. was really useful.
blakhani said
Thanks.
Database Stuck in “In Recovery” state After Restart (SQL Server) – Always SQL with Kundan Dasange said
[…] of all check the SQL Server ERRORLOG .In Errorlog, we should see the very first message in the database (is the name of the […]
3 SQL Server ERRORLOG must have configurations + 7 useful techniques | SQLHouse.com said
[…] Here is a good screenshot about the rest of SQL Server error. […]