Searching through the SQL Server Errorlogs

Sorry – It’s been a while since my last post. The past few months have been very busy for me. The other day, I had a brief discussion with a colleague on ways to search through all of the error logs in SQL Server. Obviously there are a few ways to do this – through PowerShell, simple Command/Batch Files, etc… Since I wrote an article on the parameters of xp_readerrorlog here:

The Parameters of xp_readerrorlog
https://blogs.msdn.com/b/askjay/archive/2010/09/19/the-parameters-of-xp-readerrorlog.aspx

I thought I’d go ahead and extend that methodology in a new post. A few things on this first… This is nice for DBAs because it’s likely to use the skills a DBA is already familiar with – namely TSQL. It also runs from within SQL Server instead of running a PowerShell or Batch File script from within the shell. However, just to be clear – we have announced that Extended Stored Procedures are being deprecated in SQL Server 2008 R2. For more information on this:

Deprecated Database Engine Features in SQL Server 2008 R2
https://msdn.microsoft.com/en-us/library/ms143729.aspx

Work going forward should be done using CLR stored procedures. I do not know whether or not we’ll be providing a CLR replacement for this out of the box. If not, it would be a fairly simple procedure to write.

With xp_readerrorlog, we can only read 1 error log at time by using the first parameter to specify the file extension and the second to specify whether we wish to read the SQL Server Errorlog or the SQL Agent Errorlog. To read all the logs – we just need to loop through reading all the logs and load them into a table (or return multiple result sets if we are ok with this). Below is a script that does just this:

    if exists (select * from tempdb.sys.tables where name like '#Errorlog%')
drop table #Errorlog;

    create table #ErrorLog
(
LogDate datetime,
ProcessInfo nvarchar(16),
[Text] nvarchar(2048)
)
go

    declare @p1 nvarchar(64);
declare @p2 nvarchar(64);
declare @logNum int;
declare @NumErrorLogs int;

    -- add search conditions here
select @p1 = null;
select @p2 = null;

--get the value of max errorlogs from registry
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT

    -- init counter
set @logNum = 0;

    while (@logNum <= @NumErrorLogs)
begin
declare @exists int
exec xp_fileexist 'c:\windows\notepad.exe', @exists out
if (@exists = 1)
begin
insert into #Errorlog
exec xp_readerrorlog @logNum, 1, @p1, @p2
end
select @logNum = (@logNum + 1);
end

    select * from #ErrorLog

Briefly, we check to see if the table exists, if it does DELETE it. Then create a temporary table to hold our executions of xp_readerrorlog. Next, we read the registry to get the configured max number of Errorlogs for our loop. Then we loop and load each execution into the temporary table. Once everything is in the temporary table we can obviously narrow down items further by adding a predicate to our SELECT statement.

You could encounter a problem with this for servers that do not have all the log files configured by the maximum value set in SSMS. This situation would arise with new servers and with servers that have recently had this value increased. Therefore, with each loop we test to verify the file exists before attempting to execute xp_readerrorlog with xp_fileexist.

Remember, there are many ways to accomplish this. I hope this is useful for those that like using xp_readerrorlog.

-Jay