Perl for System Administration

Monitoring Server Health

For our final set of examples, we’ll take a look at several ways to monitor the health of an SQL server. This sort of routine monitoring is similar in nature to the network service monitoring we saw in Chapter 5.

Space Monitoring

To get technical for a moment, database servers are places to hold stuff. If you run out of space to hold stuff, this is known as either “a bad thing” or “a very bad thing.” As a result, programs that help us monitor the amount of space allocated and used on a server are very useful indeed. Let’s look at a DBI program designed to look at the space situation on a Sybase server.

Here’s a snippet of output from a program that shows graphically how space is used in each database on the server. Each section shows a bar chart of the percentage of allocated data and log space in use in a database. In the following chart, d stands for data space and l stands for log space. For each bar the percentage of space used and the total available space is indicated:

|ddddddd                                          |15.23%/5MB
hpotter--------|                                                 |
               |                                                 |0.90%/5MB

               |ddddddd                                          |15.23%/5MB
dumbledore-----|                                                 |
               |                                                 |1.52%/5MB


               |dddddddd                                         |16.48%/5MB
hgranger-------|                                                 |
               |                                                 |1.52%/5MB

               |ddddddd                                          |15.23%/5MB
rweasley-------|                                                 |
               |l                                                |3.40%/5MB

               |ddddddddddddddddddddddddddd                      |54.39%/2MB
hagrid---------|                                                 |
               |- no log                                         |

Here’s how we generated this output:

use DBI; $admin = 'sa'; print "Enter passwd for $admin: "; chomp($pw = <STDIN>); $pages = 2; # data is stored in 2k pages # connect to the server $dbh = DBI->connect('dbi:Sybase:',$admin,$pw); ...