Couple of days ago, I had conversation with a Jr.DBA regarding SQL Server Error Logs! She was confused with “Error Logs” under SQL Server Agent Node and “SQL Server Logs” under Management Node. In Fact, she was assuming that both are same which is not the case!!!
Also, I heard many times from many confused DBA’s that SQL Server Error logs can’t be accessed when the SQL Services are Offline, which is absolute Bogus!! SQL Server Error log is nothing but a plain Text file located in our “LOG” folder by default which can be opened using Notepad when you are not able to connect to SQL and see via SSMS.
In this blog post, let me make it clear what/Where are SQL Server Logs and what/Where are SQL Agent logs are. Please see below Screenshot.
As you can see we’ve “SQL Server Logs” – Under Management Node and “Error Logs”- Under SQL Server Agent.
Note: All these Error Logs(SQL Server Logs and SQL Agent Logs and any Dumps being created) will be located in “C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Log” location. – In my case, I’ve SQL Server 2008R2 and a named Instance ‘PROD’, hence folder “MSSQL10_50.PROD”. “LOG” folder is not for storing your Transaction Logs!!!
If you are confused even now, You can use T-SQL to read your Error Log.
How to read SQL Server Error Log from T-SQL??
Method 2: EXEC SP_READERRORLOG – This is UnDocumented way! But this gives lot more flexibility to select the number and filter the contents etc…For example I want to read the contents from my 5th Error Log which has the word “Master”. For this, all i want to enter in my Query Pane is Exec Sp_ReadErrorlog 5, 1, ‘master‘. As you can see below I got only 4 records returned where ever word “master” is present.
Hope this helps…!