How to check SQL Server Error logs

When you are trying to troubleshoot problems in SQL Server, one of the best places to check is SQL Server Error logs.  These will have basic information on the issue, what was the source of the problem, what time did it occur so on and so forth.  In order to get to the SQL Server Error log, you would need to do connect to the instance and the following:

Server - Management - SQL Server Logs - Current

I have included a screen shot of this right below:

When you go ahead and double click on one of the SQL Server Error logs, it will open up a new Log File Viewer. This will not only contain information on SQL Server Logs, but also SQL Server Agent Logs, Database Mail and Windows Logs like Application, Security and System Logs.  Here’s a screen capture of what I’m talking about:

You will notice that on the left pane, you can choose which particular log you want to work with.  Within this log, you will see a Current Log and a set of Archive Logs.  Every time SQL Server or the SQL Server Agent is restarted, SQL Server goes ahead and recycles the log and creates a new one.  In this manner you can go back in time to see what issues happened with SQL Server.

On the right side you will have the details pane with information on particular SQL Server related event, when it happened? what was the source? which SQL Server process generated it ETC.  You can further filter these results if you like, you can search for a particular string and even export this SQL Server log to a text file if you need to save it or email it to somebody.

Sometimes the SQL Server error log does not load properly using the SQL Server Management Studio.  For these occasions, SQL Server has an undocumented feature that will let you let you load up SQL Server logs and SQL Server Agent logs using an extended stored procedure.  The procedure is this one xp_readerrorlog and I have included an example of how to use this.



/*------------------------------------------------------------------------------------------------------------

In this script we are looking at two important stored procedures

-master..xp_readerrorlog
-master..xp_fixeddrives

we cover xp_cmdshell in this script using_xp_cmdshell_0011.sql

------------------------------------------------------------------------------------------------------------*/



--reads the current sql server log

exec master..xp_readerrorlog
exec master..xp_readerrorlog 0, 1


--reads the previous sql server log

exec master..xp_readerrorlog 1, 1




--reads the current sql server agent log

exec master..xp_readerrorlog 0, 2


--reads the current sql server agent log

exec master..xp_readerrorlog 1, 2



--get information on disk drives from within sql server


exec master..xp_fixeddrives

/* --OUTPUT

C    30371
D    25803
Q    1011
R    189
S    80834
T    19013

*/


For more information on SQL Server tips and tricks, please visit our site on SQL Server 2008
http://sqlserver2008tutorial.com/

TAGS include SQL Server Error logs, SQL Server Agent logs, Windows NT Log

PS. Happy Birthday baby Sofia, she turns 6 today!!