I will describe what’s my understanding of Base Monitoring for SQL Server.
Last Thursday 5/7/2015 I presented these thoughts at a PASS regional Chapter in Cologne/Germany.
„My“ Base Monitoring consists of monitoring all Events that come up from too few hard disk space, memory or CPU. It is also about monitoring of locking, blocking or deadlocks, long running queries/jobs and failure in logins.
I will also do additional tasks that didn’t belong to the monitoring itself but help in failure check and reorganization of indexes and so on (…)
All this was arranged and written down after an installation of a new SQL Server.
These are the steps I did:
1. create a DBA Database for several scripts and tables (see below)
2. install the main script from dbWarden for Monitoring
3. install Integrity check and Maintenance scripts from Ola Halengren into DBA Database
4. create GetErrorLogEvent Stored Procedures and Job for Mails out of the SQL Errorlog into DBA Database
5. create Jobs for recycling ErrorLogs
6. Performance Monitoring, Event Logs with an external Tool called Host Monitor
The dbWarden Script (2.) is a great ressource for real time monitoring blocking, deadlocks, long running querys, jobs and so on – best of all: it is free of charge (!). It also brings a detailed and configurable health report of the server that will be sent every morning.
The scripts from Ola Halengren (3) are well known in the SQL Server Community for bringing us perfect maintenance tasks for index and statistic updates with rebuild online/offline or reorganize where it is neccessary.
Additionally there is a script for integrity check – and backup of course. 🙂
The GetErrorLogEvent Script (4.) was originally written by Jonathan Kehayias and published on an MSDN Archive site. I didn’t found the site any more on MSDN so the script is attached together with the presentation (Sorry, only german these days) below.
For recycling the Error Log (5.) it is only neccessary to create a job with the two commands (exec dbo.sp_cycle_errorlog and exec dbo.sp_cycle_agent_errorlog).
The only tool that is not free of charge is the last one, called Host Monitor.
It is perfect for more monitoring especially for long term monitoring.
Download, Wiki: http://sourceforge.net/projects/dbwarden/
GetErrorLogEvents and Basis Monitoring Presentation from the PASS Chapter in Cologne (German)
Thanks for reading,