Read SQL Server Logs with TSQL

linzieo, 2016-12-30 (first published: 2016-12-13)

The undocumented procedure XP_INSTANCE_REGREAD normally reads exactly one log and searches for entries based on the input parameters:

@lognum: Number of the error log

@logtype: 1 = Server, 2 = Agent

@string1 and @string2: Strings o searchfor in the log

The procedure presented here reads all logs for the search criteria removing the need to try and isolate te offending log entry.

The inital drop table is from SQL Server 2016. If you are using a lesser versio thereplace this with the appropriate IF EXISTS statement.

create procedure sp_readerrors as declare @string1 nvarchar(64) = null; declare @string2 nvarchar(64) = null; declare @logNum int = 0; declare @logtype int = 1; declare @NumErrorLogs int; begin drop table if exists #Errorlog; create table #ErrorLog ( LogDate datetime, ProcessInfo nvarchar(16), Text nvarchar(2048) ) ; --count the number of error logs from the registry EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT; select @NumErrorLogs; while (@logNum < @NumErrorLogs) begin insert into #Errorlog exec xp_readerrorlog @logNum, @logtype, @string1, @string2 set @logNum = (@logNum + 1); end end --read the data out --select * from #ErrorLog

Read SQL Server Logs with TSQL

Rate

Share

Share

Rate