It’s Monday, 8 AM
Real world example. Its 8 AM on a Monday morning. Nothing was entered into Change Management that should impact your SQL world in any way. Yet, the smartphone is BLIP, BLIP BLIP with new email messages and the office phone is ringing so much it is about shake itself off the desk. Where is the first place to start looking for problems? The Error Log.
The Log Book
If you are like me, wading through scores of error logs is not my cup of tea. However, I do realize the benefit of the error log and the wealth of information it contains. There just has to be some better way of fuddling through the log.
Wouldn’t it be a great idea if there were someway that the error log could be viewed as a table? As a table, the DBA could do what good DBA’s do and write scripts to pull out information that is worth while.
Here are a list of great sites that explain how to do just that…
I borrowed from the above and others and have a few scripts that can come in handy. One thing to note, using “master.sys.xp_readerrorlog” instead of “sys.xp_readerrorlog” will give you the ability to filter on dates and the ability to sort. By default, this extended procedure will return the oldest rows first.
Error Log Location
Error Log Archive Information
Error Log Script
Of course, any of this data can be saved/ persisted to a table on the local server or on a centralized server. An example saving this to a temp table would look similar to this.
Save Error Log to ##TmpError
Although my example shows the process dumping the records to a temp table, I cant think of an actual real world example where this would have benefit. I don’t persist my error log, but if I did, I would be highly selective as to what was saved and how long I saved it.