Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The SQL Server Error Log file is one of the first places each DBA looks at in order to understand and solve the problem.
In this article, we will examine the below scenarios.
- How to read the entire Error Log file without a filter
- How to read Error Log file by filtering.
As you can see in the picture below, you can read the Error Log file without a filter.
When the Error Log File opens, you can create a filter by clicking on the Filter as you see below.
In the “Message contains text” section, type the word you want to search for in the error log file, click “Apply filter”, and then click ok.
When we did this, the result was returned as follows.
When the “Error Log” file is too complicated, we can filter what we want to see.
Sometimes we need to filter what we don’t want to see. In this way, you can read the error log file, which is free of unnecessary information.
You can perform this operation with the help of the following script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | CREATE TABLE #read_error_log ( logdate DATETIME, processinfo VARCHAR(200), errorlogtext VARCHAR(max) ) INSERT INTO #read_error_log EXEC master.dbo.Xp_readerrorlog 0, 1, N'', N'', '20160922 00:00', '20160924' SELECT * FROM #read_error_log where errorlogtext not like '%participating in an%' and errorlogtext not like '%Error: 976, Severity: 14, State: 1.%' and errorlogtext not like '%Log was backed%' ORDER BY logdate DESC TRUNCATE TABLE #read_error_log DROP TABLE #read_error_log |
As you can see in the script, the script part of “EXEC master.dbo.Xp_readerrorlog” is used to read the entire Error Log file.
We have run the above script with many parameters.
Below are the descriptions of these parameters.
| First Parameter 0 | This means we’re reading the current active log file. I recommend that you read the article “How to Increase SQL Server Error Log File Count” about the SQL Server Error Log file count. |
| Second Parameter 1 | This means that we have read SQL Server Error Log. If there were 2, we would be reading the SQL Server Agent Error Log. |
| Third Parameter N” | This is the word we want to filter in the Message part in the Error Log. We did not select this option by typing N ”. |
| Fourth Parameter N” | Same as the third parameter. If you want to search with more than one word, you set your second filter in this parameter. We did not select this option by typing N ”. |
| Fifth Parameter ‘20160922 00:00’ | Starting date. |
| Sixth Parameter ‘20160924’ | Finish date. |
In the rest of the script, you see “where errorlogtext not like” statements. We’re writing the expressions that we don’t want to see in the log file with these not like expressions.
At the end of the query, you can increase the number of queries that you do not want to see by adding AND NOT LIKE. In this way, we only get an Error Log file that will contain the information we need.
