By: Bru Medishetty | Updated: 2011-02-21 | Comments (25) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Monitoring
As a SQL Server DBA, monitoring SQL Server error logs is part of my daily routine. Manually reviewing the error logs on each server is a time consuming process, especially when working on other issues. I wanted to review the error logs when there are entries in them which need my attention. How can I accomplish this task in an automated manner by receiving emails? Read this tip to learn how to become a more proactive DBA.
Important diagnostic information is available in the SQL Server error logs. There are entries related to database backups and recovery, changes in the recovery model, unsuccessful logins, and DBCC messages are some of the various items logged in the SQL Server error log. In this tip we will look at the steps to configure automatic email alerts when there is an entry in the SQL Server error log that may need to be addressed. For this setup, I am using two stored procedures (one for monitoring and one for alerting) and a SQL Server which has Database Mail enabled, to notify via email alerts.
What does the monitoring stored procedure do?
The monitoring stored procedure's main functionality is to read the current SQL Server log into a temporary table and filter those records which are older than certain number of minutes. The data which is in the rows format in the temporary table is concatenated in the form of a paragraph and then finally inserted into a table.
How does the monitoring stored procedure work?
Let's take a look at how this monitoring stored procedure works. The stored procedure accepts an integer value as a parameter, which is used later to filter the data from the error log. Then it reads the current SQL Server error log and writes the data into a temporary table. The data in the temporary table is deleted when it is older than the minutes passed as parameter, or 6 minutes, from the current time. SQL Server startup messages and database backup entries written into error log file are also deleted.
The next step in the procedure stores the error log information in the temporary table to a local variable @ERRORMSG. In a period of 5 minutes, there can be multiple entries into the error log. I used COALESCE and CHAR(13) carriage return to retain the separate error messages in a new line when the alert is sent. Once the records in the temporary table are added into the variable, the temporary table is dropped. The same set of code repeats twice, that is because the sp_readerrorlog output varies in SQL Server 2000 and later versions. The next statement checks if the variable @ERRORMSG is null, if it is not null then there are some error log entries that have to be alerted, so it stores that value in a local table in the alerts database.
Creating the monitoring stored procedure
Use the code below to create the stored procedure and remember to make changes as per your system such as the Database Mail profile, database name where the table is to be created, email id's to be notified, etc.
CREATE PROCEDURE [dbo].[SQL_ErrorLog_Alert] @Minutes [int] = NULL AS BEGIN SET NOCOUNT ON; DECLARE @ERRORMSG varchar(8000) DECLARE @SNO INT DECLARE @Mins INT DECLARE @SQLVERSION VARCHAR(4) IF @Minutes IS NULL -- If the optional parameter is not passed, @Mins value is set to 6 SET @Mins = 6 ELSE SET @Mins = @Minutes /* Fetches the numeric part of SQL Version */ SELECT @SQLVERSION = RTRIM(LTRIM(SUBSTRING(@@VERSION,22,5))) IF @SQLVERSION = '2000' /* Checks the version of SQL Server and executes the code depending on it since the output of the sp_readerrorlog varies between SQL 2000 and the next versions */ BEGIN /*Temporary table to store the output from execution of sp_readerrorlog */ CREATE Table #ErrorLog2000 (ErrorLog varchar(4000),ContinuationRow Int) INSERT INTO #ErrorLog2000 -- Stores the output of sp_readerrorlog EXEC sp_readerrorlog /* The code below deletes the rows in the error log which are mostly the SQL startup messages written into the error log */ DELETE FROM #ErrorLog2000 WHERE (LEFT(LTRIM(ErrorLog),4) NOT LIKE DATEPART(YYYY,GETDATE()) AND ContinuationRow = 0) OR (ErrorLog LIKE '%Intel X86%') OR (ErrorLog LIKE '%Copyright %') OR (ErrorLog LIKE '%Microsoft %') OR (ErrorLog LIKE '%All rights reserved.%') OR (ErrorLog LIKE '%Server Process ID is %') OR (ErrorLog LIKE '%Logging SQL Server messages in file %') OR (ErrorLog LIKE '%Errorlog has been reinitialized%') OR (ErrorLog LIKE '%Starting up database %') OR (ErrorLog LIKE '%SQL Server Listening %') OR (ErrorLog LIKE '%SQL Server is ready %') OR (ErrorLog LIKE '%Clearing tempdb %') OR (ErrorLog LIKE '%Recovery %') OR (ErrorLog LIKE '%to execute extended stored procedure %') OR (ErrorLog LIKE '%Analysis of database %') OR (ErrorLog LIKE '%Edition%') OR LEN(ErrorLog) < 25 OR (CAST(LEFT(LTRIM(ErrorLog),23) AS DATETIME) < CAST(DATEADD(MI,[email protected],GETDATE()) AS VARCHAR(23))) /* Once the SQL Server startup and other information prior to @Mins is deleted from the temporary table, the below code starts concatenating the remaining rows in the temporary table and stores into single variable */ SELECT @ERRORMSG = COALESCE(@ERRORMSG + CHAR(13) , '') + ErrorLog FROM #ErrorLog2000 DROP TABLE #ErrorLog2000 END ELSE BEGIN CREATE TABLE #ErrorLog2005 (LogDate DATETIME, ProcessInfo VARCHAR(50) ,[Text] VARCHAR(4000)) INSERT INTO #ErrorLog2005 EXEC sp_readerrorlog DELETE FROM #ErrorLog2005 WHERE LogDate < CAST(DATEADD(MI,[email protected],GETDATE()) AS VARCHAR(23)) OR ([Text] LIKE '%Intel X86%') OR ([Text] LIKE '%Copyright%') OR ([Text] LIKE '%All rights reserved.%') OR ([Text] LIKE '%Server Process ID is %') OR ([Text] LIKE '%Logging SQL Server messages in file %') OR ([Text] LIKE '%Errorlog has been reinitialized%') OR ([Text] LIKE '%This instance of SQL Server has been using a process ID %') OR ([Text] LIKE '%Starting up database %') OR ([Text] LIKE '%SQL Server Listening %') OR ([Text] LIKE '%SQL Server is ready %') OR ([Text] LIKE '%Clearing tempdb %') OR ([Text] LIKE '%to execute extended stored procedure %') OR ([Text] LIKE '%Analysis of database %') OR ProcessInfo = 'Backup' -- Deletes backup information SELECT @ERRORMSG = COALESCE(@ERRORMSG + CHAR(13) , '') + CAST(LogDate AS VARCHAR(23)) + ' ' + [Text] FROM #ErrorLog2005 DROP TABLE #ErrorLog2005 END IF @ERRORMSG IS NOT NULL -- There is some data in SQL error log that needs to be stored BEGIN IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SQL_ErrorLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) INSERT INTO [dbo].[SQL_ErrorLog] SELECT @ERRORMSG ELSE BEGIN CREATE TABLE [dbo].[SQL_ErrorLog]( [TEXT] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] INSERT INTO [dbo].[SQL_ErrorLog] SELECT @ERRORMSG END END ELSE -- No error messages have been in the last @Mins minutes Print 'No Error Messages' END GO
Once the stored procedure is in place on all production servers, I created a SQL Server Agent Job that executes this stored procedure and scheduled it to execute at a 5 minute interval. My next step was to configure my alerting server to send the email alerts.
How does the alerting stored procedure work?
This stored procedure is created on the SQL Server where Database Mail is enabled. It uses Linked Servers to pull the error log data and send alerts. For more information on Database Mail, please read the tips included under Next Steps at the end of this tip.
The alerting procedure has an optional parameter, when it is not passed; the procedure alerts the local server's error messages. When a linked server name is passed, that server's error messages are alerted, with the Linked Server name in the Subject (to identify which server had the error message).
A temporary table is created that would be used to store the record(s) from the Linked Server table. An Execute statement is used to insert the data into the temporary table. In the next step, a cursor is used in order to fetch the data from the temporary table and send an email alert. I am using the system stored procedure msdb.dbo.sp_send_dbmail to send the email alerts. Once all the rows in the temporary table are processed and alerts are sent, the temporary table is dropped and the records in the Linked Server's table are deleted.
When I first started writing the procedure, I did not use the cursor and assumed there would be one record at any time (since my alerting procedure would poll every 5 minutes and delete the records after processing). But was later proven wrong when the alerting server was under maintenance for an hour or so and one of the servers had multiple error log entries in that time period.
Creating the alerting stored procedure
CREATE PROCEDURE [dbo].[SQL_ErrorLog_Alert_Notify] @LKDSVRNAME VARCHAR(128) = NULL AS BEGIN SET NOCOUNT ON; DECLARE @SUBJECT VARCHAR(8000) DECLARE @MSGBODY VARCHAR(8000) IF @LKDSVRNAME IS NOT NULL /* A linked server name is passed, so alert will be for the linked server.*/ BEGIN CREATE TABLE #ERRTBL ([TEXT] VARCHAR(8000)) /* Temporary table to poll the linked server and store the error data to be used in the cursor, in the next step */ INSERT INTO #ERRTBL EXEC('SELECT [Text] FROM ' + @LKDSVRNAME + '.AlertsDB.DBO.SQL_ErrorLog') SET @SUBJECT = @LKDSVRNAME + ' SQL-SERVER ERROR LOG SUMMARY' DECLARE CURSOR1 CURSOR FOR SELECT [TEXT] FROM #ERRTBL OPEN CURSOR1 FETCH NEXT FROM CURSOR1 INTO @MSGBODY WHILE @@FETCH_STATUS = 0 BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL ALERTING PROFILE', -- Modify the profile name @recipients = '[email protected]',-- Modify the email @body = @MSGBODY, @subject = @SUBJECT ; FETCH NEXT FROM CURSOR1 INTO @MSGBODY END CLOSE CURSOR1 DEALLOCATE CURSOR1 EXEC ('DELETE FROM ' + @LKDSVRNAME + '.AlertsDB.DBO.SQL_ErrorLog') -- Modify the database name DROP TABLE #ERRTBL END ELSE /* A linked server name is not passed, so alert will be for the local server. */ BEGIN SET @SUBJECT = @@SERVERNAME + ' SQL-SERVER ERROR LOG SUMMARY' DECLARE CURSOR1 CURSOR FOR SELECT [TEXT] FROM AlertsDB.DBO.SQL_ErrorLog OPEN CURSOR1 FETCH NEXT FROM CURSOR1 INTO @MSGBODY WHILE @@FETCH_STATUS = 0 BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL ALERTING PROFILE', -- Modify the profile name @recipients = '[email protected]',-- Modify the email @body = @MSGBODY, @subject = @SUBJECT ; FETCH NEXT FROM CURSOR1 INTO @MSGBODY END CLOSE CURSOR1 DEALLOCATE CURSOR1 DELETE FROM AlertsDB.DBO.SQL_ErrorLog -- Modify the database name END END
I mentioned in the code where you need to make changes before implementing, they are; "database name" where the SQL_ErrorLog table is created, "Database Mail profile" and the "recipients email address". This stored procedure is then scheduled on my alerting server at a 5 minute interval. The following code needs to be included in the job step (with Linked Server names).
EXEC [dbo].[SQL_ErrorLog_Alert_Notify] EXEC [dbo].[SQL_ErrorLog_Alert_Notify] @LKDSVRNAME = 'LinkedServer1' EXEC [dbo].[SQL_ErrorLog_Alert_Notify] @LKDSVRNAME = 'LinkedServer2'
SummaryIn the past, error log entries were noticed at a later time (when looked at the SQL Server log manually at regular intervals), but never was an issue noticed within minutes of its occurrence. After configuring this automatic alerting, I view the error logs only when I get the alert. Often, these SQL Server error log alerts have helped me respond to issues immediately and accordingly. Error log entries that I have been notified about are:
- Transaction log being full causing a database backup failure
- An error such as SQL Server has encountered x number of occurrence(s) of I/O requests taking longer than 15 seconds to complete
- DBCC CHECKDB printed errors
- and so on.
- Read the following Tip Setting up Database Mail for SQL 2005.
- Read more Tips on Database Mail on MSSQL Tips here
- Consider implementing the solution outlined in this tip for monitoring the SQL Server error logs to reduce the time needed on a daily basis to monitor your SQL Server instances and to make you a more proactive DBA.
Last Updated: 2011-02-21
About the author
View all my tips
- How to setup SQL Server alerts and email operator ...
- Automate Monitoring SQL Server Error Logs with Ema...
- Automate SQL Server Monitoring with Email Alerts...
- Monitor SQL Server Database File Growth with WMI A...
- Monitor SQL Server Databases Changes Using WMI Ale...
- Monitor SQL Server Master Database Changes with WM...
- More SQL Server DBA Tips...