Reading the error logs using SSMS is usually slow, and it is also difficult to filter search results efficiently. On the other hand, TSQL can be used to get the SQL error logs (and SQL agent logs also) in a tabular format. This output can be put into a temporary table, and then investigated. There is the xp_readerrrorlog and the undocumented
sp_readerrorlog procedures that can be used. There is a nice and detailed explanation of both here. Below is a quick reference that I took from the site:
- Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
- Log file type: 1 or NULL = error log, 2 = SQL Agent log
- Search string 1: String one you want to search for
- Search string 2: String two you want to search for to further refine the results
- Search from start time
- Search to end time
- Sort order for results: N’asc’ = ascending, N’desc’ = descending
EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'desc'
EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'asc'