Monitoring ErrorLogs in SQL Server can be done a number of ways, but if you want to scan the contents in SQL and use filters to SELECT specific events from the Error Log in TSQL, it has traditionally required creating a temporary table to hold the results of xp_readerrorlog and then running your selects against this temp table. This method requires a lot of code, and isn't a fast way to pull data on the fly. Now with SQL CLR and Table Valued Functions it is simple to create a permanent solution to this problem.
The following TVF can be created to allow easy searching of the ErrorLogs on a SQL Server. It requires EXTERNAL_ACCESS since it uses the SQLClient to make a non-context connection to pull the ErrorLog information since Executing code is not allowed on the context connection. This functionality does not follow recommended best practices for implementing a TVF, but it is the only way to do this:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
/*===========================================================================
File: fn_ReadErrorLog.cs
Object Type: Table Valued User Defined Function
Permission Set: EXTERNAL_ACCESS
Summary: Retrieves the results of xp_readerrorlog for the
current instance as a table.
Parameter: LogNumber - The Log Number in the ErrorLogs to Read.
Specify NULL or 0 to refer to active log
Date: May 8, 2008
---------------------------------------------------------------------
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
============================================================================= */
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(Name = "fn_ReadErrorLog",
FillRowMethodName = "FillRowErrorLogReader",
DataAccess = DataAccessKind.Read,
TableDefinition = "LogDate datetime, ProcessInfo nvarchar(100), Text nvarchar(4000)")]
public static IEnumerable InitMethodErrorLogReader(int LogNumber)
{
List<string[]> logitems = new List<string[]>();
string servername;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select serverproperty('ServerName')";
servername = (string)cmd.ExecuteScalar();
}
conn.Close();
}
using (SqlConnection connection = new SqlConnection("Server=" + servername + ";Integrated Security=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "exec xp_readerrorlog " + LogNumber.ToString();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
string[] item = new string[3];
item.SetValue(sqlDataReader["LogDate"].ToString(), 0);
item.SetValue(sqlDataReader["ProcessInfo"].ToString(), 1);
item.SetValue(sqlDataReader["Text"].ToString(), 2);
logitems.Add(item);
}
}
return logitems;
}
public static void FillRowErrorLogReader(object obj, out SqlDateTime logDate,
out SqlString processInfo, out SqlString text)
{
string[] item = (string[])obj;
logDate = (SqlDateTime)DateTime.Parse(item[0].ToString());
processInfo = (SqlString)item[1].ToString();
text = (SqlString)item[2].ToString();
}
};
Once you create and deploy this in an Assembly, reading the errorlog is as simple as:
select * from fn_readerrorlog(0)