March 11, 2009 at 6:10 am
I have a script 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.But this script can be installed only on SQL Server 2000, I want this script for sql server 2005 also. Can anybody help me .:w00t:
Anybody can modify this scripy for sql server 2005 :
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 */
/*: possible 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_ServerNamevarchar(50),
@ls_AlertTypevarchar(25),
@ls_AlertSeverityvarchar(25),
@ls_AlertMessagevarchar(6000)
)
as
begin
/* declare local variables */
/* ----------------------- */
declare @ls_TicketNumbervarchar(15)
declare @ldt_AlertDatedatetime
declare @ls_PrimaryDBAvarchar(50)
declare @ls_PrimaryDBAPagervarchar(50)
declare @ls_PrimaryDBAWorkPhonevarchar(50)
declare @ls_PrimaryDBAHomePhonevarchar(50)
declare @ls_SecondaryDBAvarchar(50)
declare @ls_SecondaryDBAPagervarchar(50)
declare @ls_SecondaryDBAWorkPhonevarchar(50)
declare @ls_SecondaryDBAHomePhonevarchar(50)
declare @ls_PrimarySEvarchar(50)
declare @ls_SecondarySEvarchar(50)
declare @ls_PrimaryClientvarchar(50)
declare @ls_SecondaryClientvarchar(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 @lsServerNamevarchar(50)
declare @lsMessagevarchar(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),
@lsIPAddressas 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 @lsColumnvarchar(255)
declare @src varchar(255)
declare @desc varchar(255)
declare @lsCommand varchar(8000)
declare @liRowIncr int
declare @liColIncrint
declare @lsColValuevarchar(512)
declare @lsSQLvarchar(8000)
declare @liSnoint
declare @lsSetColvarchar(255)
declare @liint
/* 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 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 @lsServerNamevarchar(50)
declare @lsIPAddressvarchar(50)
declare @lsSubjectvarchar(255)
declare @lsMessagevarchar(4000)
declare @lsAlertMessagevarchar(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 = 'msdba@dynegy.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 @lsMessagevarchar(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:
March 11, 2009 at 8:30 am
Your issue is that you are using DMO in Usp_ErrorLogCheck and you would need to convert that procedure to use SMO for it to work with SQL Server 2005.
Another option would be to replace that procedure with a CLR procedure to read the Error Logs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply