My current client has a monitoring "application" written by someone for SQL Server 2000. It has been added to for following version,
but much of the code is old and written for SQL Server 2000 (i.e. not utilizing newer functionality and features).
One such function of monitoring the "application" is doing is gathering the ERRORLOG data from the SQL Server error log files every 5 minutes using sp_readerrorlog stored procedure. For those not in the know this stored procedure returns every record in the ERRORLOG file without the ability to provide a data range. It simply calls xp_readerrorlog which does have parameters for passing a date range to only retrieve records within that specific date range.
The current process builds a execution string which is then executed using osql which is a deprecated tool. The process loops through a table containing server names that have been added for monitoring then calls a stored procedure, passing the server name in as a parameter.
The current process is calling sp_readerrorlog every 5 minutes (every record), parsing the entire data set then filtering out the existing records before inserting into a log table in the database. Because he was using osql to execute, the parsing of the data was quite complex and unwieldy.
Instead of osql, I opted to write a stored procedure to select through a linked server to the servers remote servers to be monitored. I won't cover the creation of linked servers in this article, but it could be simply automated to check for the existence of a linked server with the name of the server you want to monitor and create it if it doesn't exist.
A few things before getting started: you will want to make sure your security is correct for your linked servers (meaning you probably want to make sure your SPNs are configured correctly: Register a Service Principal Name for Kerberos Connections. And you might have issues with MSDTC if not correctly configured: Troubleshooting Problems with MSDTC.
So let's get into the meat of the process. A table called sqlErrorLog exists with the following schema:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[SQLErrorLog](
[SQLErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[LogDate] [datetime] NOT NULL,
[ServerName] [varchar](255) NOT NULL,
[ProcessInfo] [varchar](100) NULL,
[Text] [varchar](8000) NULL,
CONSTRAINT [PK_SQLErrorLog] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
Frankly I hate the design, no foreign key, bad column names. I recommend creating a primary key on ymd, serverName and ukey in that order. I didn't have that luxury as this was a third party application. If you would like to change the column names
-- SELECT TOP 10000 * from sqlErrorLog el ORDER BY LogDate desc
-- exec dbo.PopulateErrorLogByServerName 'LOCAL'
ALTER PROCEDURE dbo.PopulateErrorLogByServerName (
SET NOCOUNT ON;
--IF OBJECT_ID(N'tempdb..#ErrorLogs') IS NOT NULL
-- DROP TABLE #ErrorLogs
--IF OBJECT_ID(N'tempdb..#ErrorLog') IS NOT NULL
-- DROP TABLE #ErrorLog
-- Create Temp Tables used for processing errorlog records
CREATE TABLE #ErrorLogs ([Archive #] smallint, [Date] datetime, [Log File Size (Byte] int)
CREATE TABLE #ErrorLog (ID int IDENTITY(1,1), LogDate datetime, ProcessInfo varchar(25), [Text] varchar(max), [ServerName] varchar(256))
-- Variable declaration
DECLARE @ROWS int
DECLARE @curDate datetime = getdate()
DECLARE @date nvarchar(20) = convert(varchar(20),@curDate,20)
DECLARE @datestart nvarchar(20)
DECLARE @maxdate datetime
DECLARE @FileNum tinyint
DECLARE @ExecString nvarchar(1000)
DECLARE @ParameterDefinition nvarchar(2000) = N'@FileNum tinyint, @datestart nvarchar(20), @date nvarchar(20)'
SET @ExecString = N'[' + @servername + '].[master].[sys].[xp_enumerrorlogs]'
-- Get list of errorlogs
INSERT INTO #ErrorLogs
EXECUTE sp_executesql @ExecString
SELECT @maxdate = max(LogDate)
FROM dbo.sqlErrorLog sel
WHERE serverName = @servername
IF @maxdate IS NULL
SELECT @maxdate = min(Date) FROM #ErrorLogs sel
SELECT @datestart = convert(varchar(20),@maxdate,20) -- format for execution
SELECT TOP 1 @FileNum = [Archive #]
WHERE Date <= @maxdate
ORDER BY Date desc
-- Loop through files if errorlog has been cycled (or missed)
WHILE @FileNum Is NOT NULL
set @datestart = @maxdate
SET @ExecString = N'[' + @servername + '].[master].[sys].[xp_readerrorlog] @FileNum,1,null,null, @datestart,@date,''asc'''
INSERT INTO #ErrorLog (LogDate, ProcessInfo, [Text])
EXECUTE sp_executesql @ExecString, @ParameterDefinition, @FileNum, @datestart, @date
SET [ServerName] = @servername
FROM #ErrorLog el
WHERE [ServerName] IS NULL
SELECT @FileNum = MAX([Archive #])
WHERE [Archive #] < @FileNum
-- Populate sqlErrorLog with records that do not exist
INSERT INTO sqlErrorLog ( LogDate , ProcessInfo ,Text,ServerName)
SELECT el.LogDate, el.ProcessInfo, LEFT(el.Text,8000), el.ServerName
FROM #ErrorLog el
LEFT JOIN dbo.sqlErrorLog sel
ON el.LogDate = sel.LogDate
AND el.ServerName = sel.ServerName
AND el.ProcessInfo = sel.ProcessInfo
AND el.Text = sel.Text
WHERE sel.LogDate IS NULL
SET @ROWS = @@ROWCOUNT
IF @ROWS > 0
PRINT CAST(@ROWS as varchar(8)) + ' Records Added'
PRINT '0 Records Added'