Article Topics

All Articles

Peformance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

SQL Server 2008 - Worth the Wait

SQL Server’s first significant upgrade in three years features a number of envelope-pushing enhancements and improvements. Which will have the greatest impact on SQL administration and development? More...

Scripts for Errorlog Monitoring of SQL Servers

By : Neeraj Nagpal
May 27, 2005

Printer friendly

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:\dba\SQL
set DBAOUT=E:\DBA\out
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

Text for E:\dba\SQL\errlog_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:\dba\SQL\errlog_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:\dba\out\errlog_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

Comments:

Your Name

(Emails will not be displayed on the site or used for promotional purposes)

Comment

Type characters in the image

(case sensitive)