Database monitoring

Question

Database monitoring

Michelle Cook

SSCrazy

Points: 2119
More actions
November 2, 2005 at 2:54 pm

#89817

I have been tasked with coming up with a new central database monitoring system. Currently we are using SMTP mail to alert us of errors that we have defined with the SQL Server alerts. We have a stored procedure that is executed when certain alert conditions occur and send either e-mail or page to the DBA oncall. Eventually, we want to use BMC patrol to monitor our SQL Server databases, this is what is currently being used for Oracle database monitoring. Unfortunately, we are having problems with the BMC licensing and in the interim would like to be able to monitor all our servers from a central location by using the errorlog files. What I would like to know is how other companies are handling database monitoring? Any neat home grown ideas out there?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Thayer SSChasing Mays Points: 612 More actions · Answer 1

We do something similar to monitor login failures:

We have a database with a stored procedure. This looks specificly in the active error log file on each server for the text 'Login Failure', produces a report on the server, downloads it to the central server, sends out a report. You'll need a table with the path to the error log for each server you want to monitor.

CREATE proc sp_AuditLogs as

set nocount on

exec master..xp_cmdshell 'del c:\temp\Final.txt c:\temp\Parsed.txt c:\temp\Filter.txt', no_output

--declaration

declare @today varchar(10)

declare @sql varchar(255)

declare @ServerName varchar(30), @LogPath varchar(255), @Application varchar(30)

--set date

select @today = convert(varchar(10), getdate(), 121)

--select @today

--sql

declare crsrServer cursor for

select ServerName, LogPath, Application from Servers order by ServerName asc

open crsrServer

fetch next from crsrServer into @ServerName, @LogPath, @Application

while @@FETCH_STATUS = 0

begin

--select 'Server is ' + @ServerName + ' and Logfiles reside in ' + @LogPath

select @sql = 'master..xp_cmdshell ''find /N /I "' + @today + '" < "' + @LogPath + '\ERRORLOG" > "c:\temp\Filter.txt"'', no_output'

--select @sql

exec (@sql)

select @sql = 'master..xp_cmdshell ''find /N /I "Login failed" < "c:\temp\Filter.txt" > "c:\temp\Parsed.txt"'', no_output'

exec (@sql)

if (len(rtrim(@ServerName)) + len(Rtrim(@Application))+ 13) > 50

select @sql = 'master..xp_cmdshell ''echo == ' + @Servername + '--' + @Application + '== >> "c:\temp\Final.txt"'', no_output'

else

select @sql = 'master..xp_cmdshell ''echo == ' + @Servername + '--' + @Application + replicate(' ', 50 - (len(rtrim(@ServerName)) + len(Rtrim(@Application))+ 13)) + '== >> "c:\temp\Final.txt"'', no_output'

exec master..xp_cmdshell 'echo ================================================== >> "c:\temp\Final.txt"', no_output

exec (@sql)

exec master..xp_cmdshell 'echo ================================================== >> "c:\temp\Final.txt"', no_output

exec master..xp_cmdshell 'type "C:\temp\Parsed.txt" >> "c:\temp\Final.txt"', no_output

fetch next from crsrServer into @ServerName, @LogPath, @Application

end

close crsrServer

deallocate crsrServer

set nocount off

exec master..xp_cmdshell 'type "C:\temp\Final.txt"'

GO

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 2

Michelle, David, Like yourselves we use events to trigger alerts etc, ie log file > 90%. However we are also evaluating SQL Sentry and Spotlight. By the way we use SQLLitespeed for backing up large databases. Rgds Derek.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

The MSX/TSX server setup is ideal for a great many monitoring situations. It provides centralized procedure control, scheduling and execution. At present I use it for additional SQL Server monitoring (above and beyond what MOM 2005 privides). It's also used for scheduling and execution of 'regular' database maintenance as well. A small laundry list of items the additional items monitored is:

server up (login and select @@servername, better than ping)
databases up (select via status)
daily database space monitoring (and historical strage of)
daily OS space monitoring (and historical strage of)
transaction log truncation at a threshhold (master, msdb & tempdb)
sql errorlog reporting every 30 minutes excluding specific 'normal' things)

It's probably one of the best features in SQL Server from a production management view that I can think of. Couple the monitoring with SMTP email and it's almost 'light out' management. By the way I'm presently monitoring 25 (and growing) servers with 400+ databases !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Michelle Cook SSCrazy Points: 2119 More actions · Answer 4

Rudy,

I am not familiar with the MSX/TSX server setup. Where can I get information on that? Does it come with SQL Server?

Thanks,

Michelle

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

MSX = Master server, TSX = Target server. This is a part of SQL 2000 (introduced in v7.0). Check out BOL, under the index tab enter multiserver for a start.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."