|

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


Robert Pearl @pearlknows did a blog post on SQLSaturday and sharing in November. I've been planning on doing the same for quite some time...

 From a fresh perspective, since this is the end of my first year as a SQLSaturday speaker, I want to give my perspective.

My first event was Jacksonville, FL, where so many existing speakers, Chris Skorlinski, Chad Churchwell, Bradley Balls, Rob Volk, Ed Watson were so helpful and gave such wonderful advice and feedback. This is what the SQL community is, and #SQLFamily is. In January I will be doing my first preconference session with my close buddy Ed Watson.

I've been a volunteer for years at Atlanta SQLSaturdays, serving on the organizing committee 3 years in a row. Through this I've met so many speakers that have continuously nudged me every chance they got to become a speaker as I've been a database professional for so many years that I have a lot of knowledge to share. I've had the pleasure to meet many of the people who's blogs I follow, where I've learned the most,like Denny Cherry, Wes Brown, Adam Machanic, Kalen Delaney, Stacia Misner, Kevin Kline, and many more.

You don't have to have 17 + years of experience in SQL Server to speak at a SQLSaturday, technically experience is not necessary, just the possession of information people want to hear about.I know many speakers who started out with less than a few years of SQL Server experience, but have gained so much knowledge about their topics they've become experts.As a matter of fact, I've dug into my topics and found new morsels I was unaware of during the authoring of my sessions. I like to present to beginners, they're open minded, ready to learn and stand to benefit the most from SQLSaturdays.

If you have been thinking about presenting and want some unbiased help, please don't hesitate to contact me, I'm always happy to help!

 SQLSaturday Atlanta will be coming fast next year, I hope we'll see you there!

 

 


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