The SQL Server Error Log contains a lot of information -- some of which can be very useful. :) But, how do you find what you're looking for in the Error Log quickly? How might you trace an event over time to see when it was recorded in the Error Log?
In this post I will show you how to use xp_readerrorlog to quickly find what you may be looking for in the Error Log. In this example we are looking for all incidents of 'Login Failed':
DROP TABLE #ErrorLog
CREATE TABLE #ErrorLog (
LogDate DATETIME ,
ProcessInfo VARCHAR(1000) ,
LogMessage TEXT
);
INSERT #ErrorLog -- see below for xp_readerrorlog parms
SELECT * FROM #ErrorLog
This is the output:
The above example was just login failures, but you can use this to track down just about anything that is recorded in the Error Log. I used it recently to track down the cause of database timeout exceptions. The end user could only say when the timeouts were occurring, so I looked into the error log at the given time and found this:
I/O is frozen on database XXXX. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
Then I used the above method to find the message in the Error Log every day at the same time that the full server backups were occurring. And there you have it. That was the cause of the timeouts they were seeing. Not uncommon at all to see this w/the 3rd party full server backups, which they were then able to workaround.
Anyway... this is just something you can use to quickly read the SQL Server Error Log to identify whenever a particular event is occurring. Hope you find it useful.
These are the xp_readerrorlog parameters: