Script to montior ErrorLogs every 3 hours.
This script monitors your errorlogs every 3 hours for the past 3 hours. It notifies you by email for all types of errors and by pager for all critical errors with severity type > 16. Saves lot of time when you have to monitor several servers every day 24x7.
Run this script on a dedicated centralized SQL Server for better performance. Read the instructions mentioned in the script before installing it.
/* ---------------------------------------------------------------------------------------------------- */
/* Instructions : Read the instructions below before executing this scripts. */
/* ---------------------------------------------------------------------------------------------------- */
/* Script Name : ErrorLogCheck.SQL */
/* Owner : Bodhayan K. Sharma */
/* Created On : August 09, 2002 */
/* ---------------------------------------------------------------------------------------------------- */
/* Purpose : The purpose of this script is to monitor the errorlogs of all the */
/* SQL Servers every three hours from a central database server. */
/* Email is sent as soon as an error is encountered and page is sent for */
/* Severity greater then 16. */
/* This script should be installed only on SQL Server 2000 but it can */
/* monitor version 7 also. */
/* ---------------------------------------------------------------------------------------------------- */
/* Pre-requisites : 1. Setup a SQL 2000 that will be monitoring all the other sql servers. */
/* : 2. SQL Server services should be running under domain user account */
/* : 3. Setup SQLMail to receive email notification about the errors */
/* : 4. Create a database called 'SQLHelpDesk' to store the objects created */
/* : by this script. */
/* : 5. Search SQLDBA@emailaddress.com in this script and replace it with */
/* : your email add. */
/* : 6. Contact your pager provider to activate the email feature on your */
/* pager if this feature is required.
/* ---------------------------------------------------------------------------------------------------- */
/* Execution : Press Ctrl+E to execute this scripts. */
/* ---------------------------------------------------------------------------------------------------- */
/* Post Execution : 1. Populate the serverlist table with the name of the servers that needs*/
/* : to be monitored. For IPAddress if SQL is installed as a named */
/* : instance then the ipaddress values should be ipaddress\instancename. */
/* : Populate IsSupported and IsRunning with Y. */
/* : 2. Populate the contacts table. */
/* : 3. Populate the pager number as 8001234567@skytel.com */
/* : 4. Publish SQLAlerts table to view the alerts on a webpage */
/* : 5. Use view_showerrormessage or view_showcriticalerrros to view errors */
/* : 6. Recycle the errorlogs of the servers being monitored every day if */
/* : sossible so that this script can function more effeciently. */
/* ---------------------------------------------------------------------------------------------------- */
/* Objects Created : Tables:-(serverlist,errorlog,errorloghistory,sqlalerts,contacts, */
/* : paginghistory) */
/* : Triggers:-(trigins_paginghsitory) */
/* : Stored Procedures:-(usp_executeerrorlogcheck,usp_errorlogcheck, */
/* : usp_insertsqlalerts,usp_sendpagetoprimary) */
/* : Views:-(view_showerrormessage,view_showcriticalerrors) */
/* : Jobs:-(ExecuteErrorLogCheck) - runs every three hours */
/* ---------------------------------------------------------------------------------------------------- */
/* Modified By : Bodhayan K. Sharma */
/* Modified On : August 09,2002 */
/* Modification Details : */
/* ---------------------------------------------------------------------------------------------------- */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trigIns_PagingHistory]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trigIns_PagingHistory]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_ErrorLogCheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_ErrorLogCheck]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_ExecuteErrorLogCheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_ExecuteErrorLogCheck]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_InsertSQLAlerts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_InsertSQLAlerts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_SendPageToPrimary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_SendPageToPrimary]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[View_ShowErrorMessage]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[View_ShowErrorMessage]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[View_ShowCriticalErrors]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[View_ShowCriticalErrors]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[serverlist]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[serverlist]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ErrorLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ErrorLog]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ErrorLogHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ErrorLogHistory]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQLAlerts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SQLAlerts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contacts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PagingHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PagingHistory]
GO
CREATE TABLE [dbo].[serverlist] (
[ServerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsSupported] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsRunning] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeeklyReboot] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryDBA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryPager] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryDBA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimarySE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondarySE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryClient] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryClient] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ErrorLog] (
[SNO] [int] IDENTITY (1, 1) NOT NULL ,
[ServerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorlogText] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRow] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ErrorLogHistory] (
[ServerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorlogText] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SQLAlerts] (
[TicketNumber] [bigint] NOT NULL ,
[AlertDate] [datetime] NULL ,
[AlertType] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlertSeverity] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServerName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlertMessage] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryDBA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryDBAPager] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryDBAWorkPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryDBAHomePhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryDBA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryDBAPager] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryDBAWorkPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryDBAHomePhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimarySE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondarySE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryClient] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryClient] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contacts] (
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Pager] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PagingHistory] (
[PrimaryPager] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Updated] [datetime] NULL ,
[ProblemDescription] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create View
View_ShowCriticalErrors
as
select
ServerName,
substring(errorlogtext,1,22) As Date,
errorlogtext
from
errorlog
where
servername in
(
select
distinct ServerName
from
errorlog
where
lower(errorlogtext) like "%severity: 1[789],%" or
lower(errorlogtext) like "%severity: 2[012345],%"
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create View
View_ShowErrorMessage
as
select
Substring(ServerName,1,30) as ServerName,
substring(errorlogtext,1,256) as ErrorLogText,
substring(errorlogtext,1,22) As Date
from
errorlog
where
servername in
(
select
distinct ServerName
from
errorlog
where
lower(errorlogtext) like "%backupmedium%" or
lower(errorlogtext) like "%failed%" or
lower(errorlogtext) like "%failure%"
) or
servername in
(
select
distinct ServerName
from
errorlog
where
lower(errorlogtext) like "%error: %" and
substring(errorlogtext,41,charindex(',',errorlogtext) - 41) < 100000
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create proc
Usp_InsertSQLAlerts
(
@ls_ServerName varchar(50),
@ls_AlertType varchar(25),
@ls_AlertSeverity varchar(25),
@ls_AlertMessage varchar(6000)
)
as
begin
/* declare local variables */
/* ----------------------- */
declare @ls_TicketNumber varchar(15)
declare @ldt_AlertDate datetime
declare @ls_PrimaryDBA varchar(50)
declare @ls_PrimaryDBAPager varchar(50)
declare @ls_PrimaryDBAWorkPhone varchar(50)
declare @ls_PrimaryDBAHomePhone varchar(50)
declare @ls_SecondaryDBA varchar(50)
declare @ls_SecondaryDBAPager varchar(50)
declare @ls_SecondaryDBAWorkPhone varchar(50)
declare @ls_SecondaryDBAHomePhone varchar(50)
declare @ls_PrimarySE varchar(50)
declare @ls_SecondarySE varchar(50)
declare @ls_PrimaryClient varchar(50)
declare @ls_SecondaryClient varchar(50)
/* initialize the variables here */
/* ----------------------------- */
select @ls_TicketNumber = IsNull(max(ticketnumber),0)+ 1 from SQLAlerts
set @ldt_Alertdate = getdate()
select
@ls_PrimaryDBA = PrimaryDBA,
@ls_SecondaryDBA = SecondaryDBA,
@ls_PrimarySE = PrimarySE,
@ls_SecondarySE = SecondarySE,
@ls_PrimaryClient = PrimaryClient,
@ls_SecondaryClient = SecondaryClient
from
ServerList
where
ServerName = @ls_ServerName
select
@ls_PrimaryDBAPager = Pager,
@ls_PrimaryDBAWorkPhone = WorkPhone,
@ls_PrimaryDBAHomePhone = HomePhone
from
Contacts
where
Name = @ls_PrimaryDBA
select
@ls_SecondaryDBAPager = Pager,
@ls_SecondaryDBAWorkPhone= WorkPhone,
@ls_SecondaryDBAHomePhone= HomePhone
from
Contacts
where
Name = @ls_SecondaryDBA
INSERT INTO
SQLAlerts
(
TicketNumber,
AlertDate,
AlertType,
AlertSeverity,
ServerName,
AlertMessage,
PrimaryDBA,
PrimaryDBAPager,
PrimaryDBAWorkPhone,
PrimaryDBAHomePhone,
SecondaryDBA,
SecondaryDBAPager,
SecondaryDBAWorkPhone,
SecondaryDBAHomePhone,
PrimarySE,
SecondarySE,
PrimaryClient,
SecondaryClient
)
VALUES
(
@ls_TicketNumber,
@ldt_AlertDate,
@ls_AlertType,
@ls_AlertSeverity,
@ls_ServerName,
@ls_AlertMessage,
@ls_PrimaryDBA,
@ls_PrimaryDBAPager,
@ls_PrimaryDBAWorkPhone,
@ls_PrimaryDBAHomePhone,
@ls_SecondaryDBA,
@ls_SecondaryDBAPager,
@ls_SecondaryDBAWorkPhone,
@ls_SecondaryDBAHomePhone,
@ls_PrimarySE,
@ls_SecondarySE,
@ls_PrimaryClient,
@ls_SecondaryClient
)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create proc Usp_SendPageToPrimary(@lsCheckString varchar(15),@lsSubject varchar(50))
as
begin
declare @lsPrimaryPager varchar(50)
declare @lsPrevPrimaryPager varchar(50)
declare @lsServerName varchar(50)
declare @lsMessage varchar(50)
if @lsCheckString = 'ErrorLog'
begin
declare
lcur_forEmail
cursor for
select
distinct b.ServerName,
b.primarypager
from
View_ShowCriticalErrors a,
Serverlist b
where
a.servername = b.servername
order by
b.primarypager,
b.servername
end
open lcur_forEmail
Fetch next from
lcur_forEmail
into
@lsServerName,
@lsPrimaryPager
select @lsmessage = ""
select @lsPrevPrimaryPager = @lsPrimaryPager
while (@@fetch_status = 0)
begin
select @lsMessage = @lsServerName + ','+@lsMessage
Fetch next from
lcur_forEmail
into
@lsServerName,
@lsPrimaryPager
if (@lsPrimaryPager <> @lsPrevPrimaryPager) or (@@fetch_status <> 0)
begin
select @lsMessage = @lsSubject + ' ' + @lsMessage
/* insert the message and trigger will send the email */
insert into
PagingHistory
values
(
@lsPrevPrimaryPager,
@lsMessage,
getdate(),Null
)
select @lsPrevPrimaryPager = @lsPrimaryPager
select @lsMessage = ""
end
end
close lcur_foremail
deallocate lcur_foremail
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create procedure
Usp_ErrorLogCheck
(
@lsServerName as Varchar(50),
@lsIPAddress as Varchar(50),
@lsTableName as varchar(25))
as
begin
/* Declare local variables */
/* ----------------------- */
declare @object int
declare @objQueryResults int
declare @hr int
declare @lsRow varchar(255)
declare @lsColumn varchar(255)
declare @src varchar(255)
declare @desc varchar(255)
declare @lsCommand varchar(8000)
declare @liRowIncr int
declare @liColIncr int
declare @lsColValue varchar(512)
declare @lsSQL varchar(8000)
declare @liSno int
declare @lsSetCol varchar(255)
declare @li int
/* Create an instance of SQL Server Object */
/* --------------------------------------- */
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0 goto DestroyObj
/* Connect to the instance of SQL Server object */
/* -------------------------------------------- */
-- EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @lsServerName, @lsUserID, @lsPassword
-- IF @hr <> 0 goto DestroyObj
/* nt authentication is implemented here */
EXEC @hr = sp_OASetProperty @object, 'LoginSecure',True
IF @hr <> 0 goto DestroyObj
/* This is to try again if time out or general network error occurs */
/* ---------------------------------------------------------------- */
set @li = 1
set @hr = 1
while (@li <= 5 and @hr <> 0)
begin
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @lsIPAddress
if @hr <> 0 waitfor delay '000:00:15'
set @li = @li + 1
end
IF @hr <> 0 goto DestroyObj
-- print @lsservername
/* Execute xp_fixed drives to get the free space */
/* --------------------------------------------- */
select @lsCommand = 'ExecuteImmediate("drop table tempdb..errorlogtemp")'
select @lsCommand = 'ExecuteImmediate("create table tempdb..errorlogtemp (ErrorlogText varchar(512),CRow int)")'
-- print '1..'+@lsCommand
EXEC @hr = sp_OAMethod @object,@lsCommand
IF @hr <> 0 goto DestroyObj
select @lsCommand = 'ExecuteImmediate("insert into tempdb..errorlogtemp execute master..xp_readerrorlog")'
-- print '2..'+@lsCommand
EXEC @hr = sp_OAMethod @object,@lsCommand
IF @hr <> 0 goto DestroyObj
select @lsCommand = 'ExecuteImmediate("delete from tempdb..errorlogtemp where IsDate(substring(errorlogtext,1,22)) = 0")'
-- print '3..'+@lsCommand
EXEC @hr = sp_OAMethod @object,@lsCommand
IF @hr <> 0 goto DestroyObj
/* check for past 30 minutes */
/* ------------------------- */
select @lsCommand = 'ExecuteImmediate("delete from tempdb..errorlogtemp where datediff(minute,convert(datetime,substring(errorlogtext,1,22)),getdate()) > 180")'
-- print '4..'+@lsCommand
EXEC @hr = sp_OAMethod @object,@lsCommand
IF @hr <> 0 goto DestroyObj
select @lsCommand = 'ExecuteWithResults("select * from tempdb..errorlogtemp order by convert(datetime,substring(errorlogtext,1,22))")'
-- print '5..'+@lsCommand
EXEC @hr = sp_OAMethod @object,@lsCommand,@objqueryresults OUT
IF @hr <> 0 goto DestroyObj
select @lsCommand = 'ExecuteImmediate("drop table tempdb..errorlogtemp")'
-- print '6..'+@lsCommand
EXEC @hr = sp_OAMethod @object,@lsCommand
IF @hr <> 0 goto DestroyObj
/* Get the number of rows returned from xp_fixeddrives */
/* --------------------------------------------------- */
EXEC @hr = sp_OAGetProperty @objqueryresults, 'rows', @lsRow OUT
IF @hr <> 0 goto DestroyObj
-- print @lsRow
/* Get the number of rows returned from xp_fixeddrives */
/* --------------------------------------------------- */
EXEC @hr = sp_OAGetProperty @objqueryresults, 'columns', @lsColumn OUT
IF @hr <> 0 goto DestroyObj
Select @liRowIncr = 1
while @liRowIncr <= convert(int,@lsRow)
begin
Select @liColIncr = 1
While @liColIncr <= convert(int,@lsColumn)
begin
/* Get the data from the drive column */
/* ---------------------------------- */
select @lscommand = "GetColumnString(" + convert(varchar(15),@liRowIncr) +"," + convert(varchar(15),@liColIncr) + ")"
select @lsColValue = ''
EXEC @hr = sp_OAMethod @objQueryResults,@lsCommand,@lsColValue Out
IF @hr <> 0 goto DestroyObj
-- print @lsColvalue
if @liColIncr = 1
begin
select @lsSQL = 'insert into ' + @lsTableName + '(ServerName) values ("' + @lsServerName + '")'
exec (@lsSQL)
select @lisno = @@identity
end
select @lsSetCol = col_name(Object_id(@lsTableName),@liColIncr+2)
select @lsSQL = 'update ' + @lsTableName + ' set ' + @lsSetCol +'="'+@lsColValue + '" where SNO = ' + convert(varchar(15),@lisno)
exec (@lsSQL)
-- print @lssql
select @liColIncr = @liColIncr + 1
END
Select @liRowIncr = @liRowIncr + 1
end
/* Destroy the sql server object */
/* ----------------------------- */
DestroyObj:
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
exec master..xp_logEvent 60000,@desc,Error
exec master..xp_logevent 60000,@lsServerName,Error
exec master..xp_logevent 60000,'Error while executing Usp_ErrorLogCheck procedure...',Error
END
EXEC @hr = sp_OAMethod @object, 'DisConnect'
EXEC @hr = sp_OADestroy @object
exec @hr = sp_OADestroy @objQueryResults
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create procedure Usp_ExecuteErrorLogCheck
as
begin
/* set the environment */
/* ------------------- */
set nocount on
/* declare local variables */
/* ----------------------- */
declare @lsServerName varchar(50)
declare @lsIPAddress varchar(50)
declare @lsSubject varchar(255)
declare @lsMessage varchar(4000)
declare @lsAlertMessage varchar(6000)
/* declare local cursor */
/* -------------------- */
declare
lcur_ServerList
cursor for
Select
ServerName,
IPAddress
from
ServerList
where
IsRunning = 'Y'
/* open the cursor */
/* --------------- */
open lcur_ServerList
/* fetch the first server */
/* ---------------------- */
fetch next from
lcur_ServerList
into
@lsServerName,
@lsIPAddress
/* cleaup the records from ErrorLog table */
/* --------------------------------------- */
delete from
errorlog
where
servername not in (select distinct servername from view_showerrormessage)
insert into
ErrorLogHistory ( ServerName,ErrorLogText)
Select
ServerName,ErrorLogText
from
ErrorLog
truncate table ErrorLog
/* do until all servers are fetched */
/* -------------------------------- */
while @@fetch_status = 0
begin
/* call the procedure to check the free disk space for supported servers */
/* --------------------------------------------------------------------- */
execute Usp_ErrorLogCheck @lsServername,@lsIPAddress,"ErrorLog"
set @lsAlertMessage = ''
if exists (select * from view_showcriticalerrors where servername = @lsServerName)
begin
Update
view_showcriticalerrors
set
@lsAlertMessage = IsNull(@lsAlertMessage,'') + substring(a.errorlogtext,34,len(a.errorlogtext)) + Char(13)
from
view_showcriticalerrors a
where
a.servername = @lsServerName
exec Usp_InsertSQLAlerts @lsServerName,'ErrorLog','RED',@lsAlertMessage
end
else
begin
if exists (select * from view_showerrormessage where servername = @lsservername)
begin
Update
view_showerrormessage
set
@lsAlertMessage = case when len(IsNull(@lsAlertMessage,'') + substring(a.errorlogtext,34,len(a.errorlogtext)) + char(13)) <= 6000 then IsNull(@lsAlertMessage,'') + substring(a.errorlogtext,34,len(a.errorlogtext)) + char(13) end
from
view_showerrormessage a
where
a.servername = @lsservername
exec Usp_InsertSQLAlerts @lsServerName,'ErrorLog','Yellow',@lsAlertMessage
end
end
/* fetch the next server */
/* --------------------- */
fetch next from
lcur_ServerList
into
@lsServerName,
@lsIPAddress
end
/* close the server name cursor */
/* ---------------------------- */
close lcur_ServerList
deallocate lcur_ServerList
/* Send email for disk space below 600 MB */
if exists (Select * from view_showerrormessage)
begin
select @lsSubject = 'Err:'+convert(varchar(15),getdate(),108)
set @lsMessage = ''
select distinct ServerName into #temp from View_ShowErrorMessage
update
#temp
set
@lsMessage = IsNull(@lsMessage,'') + ServerName +','
from
#temp
exec master..xp_sendmail
@recipients = 'SQLDBA@EmailAddress.com',
@Subject=@lsSubject,
@message=@lsMessage,
@Query='Select * from SQLHelpDesk..View_ShowErrorMessage order by ServerName,Date',
@width=350,
@attach_results=true
If exists (select * from SQLHelpDesk..View_ShowCriticalErrors)
begin
exec Usp_SendPageToPrimary 'ErrorLog',@lsSubject
end
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trigIns_PagingHistory
ON PagingHistory
FOR INSERT
AS
BEGIN
declare @lsPrimaryPager varchar(50)
declare @lsMessage varchar(50)
select
@lsPrimaryPager = PrimaryPager,
@lsMessage = Message
from
inserted
exec master..xp_sendmail
@recipients = @lsPrimaryPager,
@Message=@lsMessage
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* create and schedule a job to check the error logs every three hours */
/* ------------------------------------------------------------------- */
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'ExecuteErrorLogCheck')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''ExecuteErrorLogCheck'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'ExecuteErrorLogCheck'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'ExecuteErrorLogCheck', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'ErrorLogCheck', @command = N'execute SQLHelpDesk..Usp_ExecuteErrorLogCheck', @database_name = N'SQLHelpDesk', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'ExecuteErrorLogCheck', @enabled = 1, @freq_type = 4, @active_start_date = 20020215, @active_start_time = 500, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 3, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
