SQL Server Monitoring Script - Part 1
SQL Server Monitoring in a large environment can be very time consuming. You obviously have alerts set up, but what about the morning status report? Here is a simple script I use to make the job easy.This article follows on from the SQL Server Audit Script article and describes a script I use for my morning database checks.
In this article I demonstrate what my script does and provide some screenshots. In the next article I give you the code and explain how to use it.
The script is packaged as an HTA file called SQL Snapshot.hta. It reads the servers and instances to be monitored from an xml file (SQL Servers.xml). Here's what it looks like when first started: SQL Snapshot.
Initially, all servers are displayed but these can be filtered by selecting a group in the dropdown. Change the user name, if necessary, enter your password, deselect one or more servers/instances and hit Run. All options except "Disabled Jobs" are enabled by default. Clicking the word "Configuration" will select or deselect all servers or instances.
The output for each section is as follows:
The Disk Space section shows the percentage free space on each disk and looks like this: Disk Space. Disks with less than 20% free are displayed in amber. Disks with less than 10% free are displayed in red.
The Overdue Backups section shows all databases that have not been fully backed up for over 24 hours and looks like this: Overdue Backups.
The Failed Jobs section shows all enabled jobs that failed on their last run and looks like this: Failed Jobs.
The Disabled Jobs section is not run by default as it should not be needed every day. It shows all disabled jobs and looks like this: Disabled Jobs.
The Error Logs section looks at interesting messages in the logs, such as errors, server restarts, deadlocks, etc and looks like this: Error Logs. It simply displays the number of messages for each of the last 3 days; you need to look at the logs to get the details.
The Long Running Processes section reports on any process that has been running for more than half an hour and looks like this: Long Running Processes.
I realise that all of this is not much use to you without the code, so read the next article SQL Server Monitoring Script Part 2 for all the details.
