This article applies to SQL Server 2008 and later, where not noted otherwise.
Article written: 2012-07-05.
Updated 2015-04-08. I moved delete of un-interesting messages from the prepare proc to the following analysus script. Makes it more flexible and transparent.
Intended audience
You are probably a SQL Server DBA. I assume that you understand how to execute SQL statements and can handle a simple SELECT statement.
Details
This solution first creates a stored procedure that imports the SQL Server and SQL Server Agent errorlog files information into two tables (SqlLogs and AgentLogs). You then run a SELECT statement several times over this table, modifying the WHERE clause. The working process is something like:
- Run a SELECT without a WHERE clause
- Browse the result, and pick some message that happens very frequently
- Add this as a LIKE to the WHERE clause, with appropriate wildcards (%). See my commented examples.
- You now see only those messages.
- Decide what to do with them. This is of course the important part!!!
- Change the LIKE to NOT LIKE.
- You now see all messages except above, reducing the number of messages you see with each iteration.
- Repeat step 2-7 for as long as you want.
- It won't take long until you are down to a handful of messages (10-50), which you just go over from top to bottom.
Note: do not have more than one LIKE since ANDing two different LIKE will always produce a result for 0 rows! In general, you work with one message at a time, first LIKE to see all occurrences and then NOT LIKE to hide all occurrences. But you can of course modify the WHERE clause to your liking.
Keeping the errorlog file as small as possible
The fewer messages you have in the errorlog file, the better. There are several ways to keep the errorlog files as small as possible:
- Make sure this doesn't happen again. I hope this is obvious. Say you have a bunch of failed login messages. Find out why these happen and correct the configuration for that client, or whatever the reason might be. The more serious the error is, the more rewarding this type of job is, especially if you can hunt down the reason, fix the problem and make sure it doesn't happen again.
- Configure SQL Server to not write "successful backup" messages. You do this using trace flag 3226, more info here(https://msdn.microsoft.com/en-us/library/ms188396). I only do this where appropriate, typically when we don't use some 3:rd party backup software for SQL Server.
- Configure SQL Server to not write certain messages to the eventlog. You do this using sp_altermessage. I hope it is obvious that you only what to do this if this message is indeed something you have no value at all for in the eventlog!
Anywhere you like. I usually have a database named "sqlmaint" for these type of things, and this is what you find for the USE commands in my scripts. Change to your liking.
How about a centralized solution? Sure, that is possible. The simple way is to use a multi-server query windows in SSMS. Or you can have some routines to import all logs into some central server and do the analysis there. I haven't had the need for a centralized solution so far, so I haven't spent time on that. I might do something in the future...
The PrepareLogTables procedure
The first parameter is how many SQL Server logs to import (1 means only the current one, 2 means the current and the one before, etc). The second parameter is how many agent log files to import.
Note that there is a DELETE statement for each log. This remove messages that I tend not to be interested in (like startup messages). These messages can have very valuable information per se, but from the perspective of going over the errorlog file and look for out-of-the-ordinary, I prefer to remove these. Modify this WHERE clause to your liking.
The code
You first need to install the PrepareLogTables procedure(http://www.karaszi.com/sqlserver/code/analyze_sql_server_logs_PrepareLogTables.sql.txt).
Then you can use the code in here(http://www.karaszi.com/sqlserver/code/analyze_sql_server_logs.txt) to analyze your SQL Server logs.
Reference: http://www.karaszi.com/sqlserver/util_analyze_sql_server_logs.asp