Scripts for Errorlog Monitoring of SQL Servers

The text for the batch file: errorlog_chk_1.bat

============ Text of the batch file ======================
@echo OFF
@if not “%ECHO%”==”” echo %ECHO%

rem *******************************************
rem Script : errorlog_chk_1.bat
rem Author : Neeraj Nagpal
rem Date : 02/01/2005
rem *******************************************

rem Set Local scope and call main proc.

setlocal & pushd & Set RET=
set SCRIPTNAME=%~n0
set SCRIPTPATH=%~f0
set DBASQL=E:dbaSQL
set DBAOUT=E:DBAout
set SRVNAME=%1


set DEBUG=1

if “%DEBUG%”==”1″ (set TRACE=echo) else (set TRACE=rem)
if /i {%1}=={/Help} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={/?} (call :HELP %2) & (goto :HELPEXIT)

call :MAIN %*
:HELPEXIT
popd & endlocal & set RET=%RET%
goto :EOF

rem ********************************************
rem Help Information Follows

:HELP
if defined TRACE %TRACE% [proc %0 %*]
echo This script will check the availability of a server.
echo Usage : errorlog_chk_1.bat

goto :EOF

rem *******************************************
rem Main Procedure will follow

:MAIN
if defined TRACE %TRACE% [proc %0 %*]



REM **************Step 1 *** Bring the error logs of all the servers to SQLSRVCNTREP ***************************

echo “*******Date=%date%::%time% || Start of the DTS step ************” >>%DBAOUT%error_log_dts.log

dtsrun /S SQLSRVCNTREP /N err_log_chk /E >>%DBAOUT%error_log_dts.log

set err=%ERRORLEVEL%
echo Return code is %err%

echo “Return Code = %err%”>>%DBAOUT%error_log_dts.log

echo “*******End of the DTS step************” >>%DBAOUT%error_log_dts.log


REM **************End of Step 1 *** Populate the exception report table ***************************


REM **************Step 2 *** Run the sql to get the errors from errorlogs ***************************


echo “*******Date=%date%::%time% || Start of the Query execution step ************” >>%DBAOUT%error_log_dts.log



isql -S%SRVNAME% -E -i %DBASQL%errlog_chk_1.sql -o%DBAOUT%errlog_chk_1.out -n -w3000 -t120

isql -S%SRVNAME% -E -i %DBASQL%errlog_chk_1a.sql -o%DBAOUT%errlog_chk_1a.out -n -w3000 -t120

isql -S%SRVNAME% -E -i %DBASQL%errlog_chk_2.sql -o%DBAOUT%errlog_chk_2.sql -n -w3000 -t120

set err=%ERRORLEVEL%
echo Return code is %err%

rem — put the main procedure here

rem ******************************************
rem Additional info can be put here

rem ******************************************************************



Text of the SQL query in the execute task of DTS package

–For SQL Server 2000

use dbamaint
go
if not exists (select * from dbamaint..sysobjects where name=’errorlog_srv’ and type=’U’)
create table dbamaint..errorlog_srv
(server_name varchar(25) default @@servername,errlog_text varchar(255),c2 int)

if exists (select * from dbamaint..sysobjects where name=’temp_errorlog’ and type=’U’)
drop table dbamaint..temp_errorlog

create table dbamaint..temp_errorlog
(server_name varchar(25) default @@servername,errlog_text varchar(255),c2 int)

insert dbamaint..temp_errorlog (errlog_text,c2)
exec master..xp_readerrorlog

if exists (select * from dbamaint..sysobjects where name=’new_errorlog_rows’ and type=’U’)
drop table dbamaint..new_errorlog_rows

create table dbamaint..new_errorlog_rows
(server_name varchar(25) default @@servername,errlog_text varchar(255),c2 int)

insert dbamaint..new_errorlog_rows (errlog_text,c2)
select errlog_text,c2 from dbamaint..temp_errorlog where errlog_text not in (select errlog_text from dbamaint..errorlog_srv)

–select * from dbamaint..new_errorlog_rows

insert dbamaint..errorlog_srv
select * from dbamaint..new_errorlog_rows


–For SQL Server 6.5

use dbamaint
go
delete dbamaint..temp_errorlog
go
insert dbamaint..temp_errorlog (errlog_text,c2)
exec master..xp_readerrorlog
go
delete dbamaint..new_errorlog_rows
go
insert dbamaint..new_errorlog_rows (errlog_text,c2)
select errlog_text,c2 from dbamaint..temp_errorlog where errlog_text not in (select errlog_text from dbamaint..errorlog_srv)
go
–select * from dbamaint..new_errorlog_rows

insert dbamaint..errorlog_srv
select * from dbamaint..new_errorlog_rows



Text for errlog_chk_1.sql

set nocount off

use dbamaint
go
select * from errorlog_all_srv where errlog_text not in (select errlog_text from errorlog_all_srv1)
and (errlog_text like ‘%error%’ or errlog_text like ‘%fail%’ ) and errlog_text not like ‘%backup%’
and errlog_text not like ‘%Logging SQL Server messages%’
and errlog_text not like ‘%Error: 15457, Severity: 0%’
and errlog_text not like ‘%Error : 1608, Severity: 21%’
and errlog_text not like ‘%Error : 17824, Severity: 10%’
order by server_name
go
WAITFOR DELAY ’00:01:00′
go



Text for E:dbaSQLerrlog_chk_1a.sql

set nocount off
exec dbamaint..sp__serverrlog
go
WAITFOR DELAY 00:01:00
Go

create proc sp__serverrlog
as
set nocount off
declare @srvname nvarchar(100)
declare @datehr nvarchar(100)

declare c1 cursor for
select distinct ‘telalrtc -g dbaoncall -host telalert_srv -m ‘+server_name+’ log segment space is full’,substring(errlog_text,1,13) from dbamaint..errorlog_all_srv where
errlog_text not in (select errlog_text from errorlog_all_srv1)
and errlog_text like ‘%1105%’
order by ‘telalrtc -g dbaoncall -host telalert_srv -m ‘+server_name+’ log segment space is full’
open c1

fetch next from c1 into @srvname,@datehr

while (@@fetch_status = 0)
begin
exec master..xp_cmdshell @srvname
fetch next from c1 into @srvname,@datehr
end

close c1
deallocate c1

declare c2 cursor for
select distinct ‘telalrtc -g dbaoncall -host telalert_srv -m ‘+server_name+’ default segment space is full’,substring(errlog_text,1,13) from dbamaint..errorlog_all_srv where
errlog_text not in (select errlog_text from errorlog_all_srv1)
and errlog_text like ‘%9002%’
order by ‘telalrtc -g dbaoncall -host telalert_srv -m ‘+server_name+’ default segment space is full’
open c2

fetch next from c2 into @srvname,@datehr

while (@@fetch_status = 0)
begin
exec master..xp_cmdshell @srvname
fetch next from c2 into @srvname,@datehr
end

close c2
deallocate c2


declare c3 cursor for
select distinct ‘telalrtc -g dbaoncall -host telalert_srv -m ‘+server_name+’ : I/O errors in errorlog’,substring(errlog_text,1,13) from dbamaint..errorlog_all_srv where
errlog_text not in (select errlog_text from errorlog_all_srv1)
and (errlog_text like ‘%I/O error%’ or errlog_text like ‘%Device activation error%’)
order by ‘telalrtc -g dbaoncall -host telalert_srv -m ‘+server_name+’ : I/O errors in errorlog’
open c3

fetch next from c3 into @srvname,@datehr

while (@@fetch_status = 0)
begin
exec master..xp_cmdshell @srvname
fetch next from c3 into @srvname,@datehr
end

close c3
deallocate c3



Text for E:dbaSQLerrlog_chk_2.sql

set nocount off

use dbamaint
go
select * from errorlog_all_srv where errlog_text not in (select errlog_text from errorlog_all_srv1)
and (errlog_text like ‘%error%’ or errlog_text like ‘%fail%’ ) and errlog_text not like ‘%backup%’
and errlog_text not like ‘Logging SQL Server messages%’
and errlog_text not like ‘%Error: 15457, Severity: 0%’
and errlog_text not like ‘%Error : 1608, Severity: 21%’
and errlog_text not like ‘%Error : 17824, Severity: 10%’
order by server_name
if @@rowcount >1
exec master..xp_cmdshell ‘blat E:dbaouterrlog_chk_1.out -t neeraj.nagpal@neeraj.com -s “Errors found in one or more SQL Server errorlog” -server timeline -f neeraj.nagpal@neeraj.com’
go
insert errorlog_all_srv1
select * from errorlog_all_srv where errlog_text not in (select errlog_text from errorlog_all_srv1)
go




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |