|

SQL Server and Other Topics

rss

I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver


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 
GO 
 
SET QUOTED_IDENTIFIER ON 
GO 
 
SET ANSI_PADDING ON 
GO 
 
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  
( 
   [LogDate] ASC, 
   [ServerName] ASC, 
   [SQLErrorLogID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 
 
GO 
 
SET ANSI_PADDING OFF 
GO
   
 

 

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 ( 
   @servername varchar(100) 
   ) 
   
AS 
   
SET NOCOUNT ON; 
BEGIN 
   --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)' 
   PRINT @ExecString 
  
  
    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 #] 
   FROM #ErrorLogs  
   WHERE Date <= @maxdate 
   ORDER BY Date desc 
 
   -- Loop through files if errorlog has been cycled (or missed) 
   WHILE @FileNum Is NOT NULL 
   BEGIN 
   
   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  
   
   UPDATE el 
   SET [ServerName] = @servername 
   FROM #ErrorLog el 
   WHERE [ServerName] IS NULL 
   
   SELECT @FileNum = MAX([Archive #]) 
   FROM #ErrorLogs  
   WHERE [Archive #] < @FileNum 
   
   END 
   
   -- 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' 
   ELSE 
   PRINT '0 Records Added' 
   
END