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