| Category: | Database |
| Author/Contact Info | Chris Winters <chris@cwinters.com> |
| Description: | Output a warning report if either the log or data devices for a particular database get less than x% empty. If you put this in a cron job (recommended), the report will be emailed to you. Sample usage is down toward the bottom of the script. Note that this only works for Sybase, since it uses the Sybase-specific stored procedure sp_helpdb dbname. |
#!/usr/bin/perl -w # Purpose: ensure that data/log devices don't get filled # up for a particular database. This lets you know if the # empty percent gets below a particular threshold. # # It is meant to be run as cron job, although you might want to put # the actual call into a shell script so you don't have # usernames/passwords floating around... # Copyright (c) 2000 intes.net, inc. use strict; use DBI; use Data::Dumper qw( Dumper ); use Number::Format; use Getopt::Long qw( GetOptions ); # Yes, I know that Getopt::Long allows you to declare defaults for # options, but we need to reference the default variable in usage() be +low my $DEFAULT_WARN_PERCENT = 30; { # Get all the options and ensure they're consistent my ( $OPT_verbose, $OPT_help, $OPT_warn_percent ); my ( $OPT_database, $OPT_server, $OPT_user, $OPT_password ); my ( $OPT_sybase ); GetOptions( 'help' => \$OPT_help, 'verbose+' => \$OPT_verbose, 'warn_percent=s' => \$OPT_warn_percent, 'sybase=s' => \$OPT_sybase, 'D=s' => \$OPT_database, 'S=s' => \$OPT_server, 'U=s' => \$OPT_user, 'P=s' => \$OPT_password ); if ( $OPT_help ) { print usage(); exit(0) } $ENV{SYBASE} = $OPT_sybase if ( $OPT_sybase ); $OPT_warn_percent ||= $DEFAULT_WARN_PERCENT; $OPT_warn_percent = $OPT_warn_percent / 100 if ( $OPT_warn_percent > + 1 ); # Open up the db handle and execute the sp my $DSN = "database=$OPT_database"; $DSN .= ";server=$OPT_server" if ( $OPT_server ); my $dbh = DBI->connect( "DBI:Sybase:$DSN", $OPT_user, $OPT_password, { RaiseError => 0, ChopBlanks => 1 } ) || die "Cannot connect!\nError: $DBI::errstr\n"; $dbh->{RaiseError} = 1; my ( $sth ); eval { $sth = $dbh->prepare( "sp_helpdb $OPT_database" ); $sth->execute; }; if ( $@ ) { die "Could not prepare/execute stored procedure for finding db info +: $@"; } # Keep track of the rows -- the first is general db information; 2 # .. n are information about the devices, n+1 .. last - 1 are segment # info (sometimes) and the last is just the return status, which # should always be 0 my $row_count = 1; my $db_info = {}; # Note that we use the DBD::Sybase-specific method for fetching # multiple result sets from the same query do { while ( my $row = $sth->fetchrow_arrayref ) { # The first item is the overall definition if ( $row_count == 1 ) { $db_info->{name} = $row->[0]; $db_info->{owner} = $row->[2]; $db_info->{device_num} = $row->[3]; $db_info->{created_on} = $row->[4]; $db_info->{options} = $row->[5]; $db_info->{total_size} = translate_mb_to_bytes( $row->[1] ); } # These describe the space used on the devices # 0 = name; 1 = 'xxx MB' (size); 2 = type; 3 = size free (in page +s??) elsif ( $row->[3] ) { my $device_size = translate_mb_to_bytes( $row->[1] ); my $type = ( $row->[2] =~ /^log/ ) ? 'log' : 'data'; push @{ $db_info->{devices} }, { type => $type, name => $row->[ +0], total_size => $device_size, free_size => $row->[3] * 1000 + }; $db_info->{"${type}_size"} += $device_size; $db_info->{"${type}_free"} += $row->[3] * 1000; } $row_count++; } } while ( $sth->{syb_more_results} ); # Don't need the db handle anymore $dbh->disconnect; # Calculate the space used $db_info->{log_used} = $db_info->{log_size} - $db_info->{log_free}; $db_info->{data_used} = $db_info->{data_size} - $db_info->{data_free} +; # Now display some info -- we only display the message if either our # log or data devices is below our warning percentage my $log_empty = $db_info->{log_free} / $db_info->{log_size}; my $data_empty = $db_info->{data_free} / $db_info->{data_size}; if ( $log_empty < $OPT_warn_percent or $data_empty < $OPT_warn_percen +t ) { # Open a Number format object so we can format consistently my $x = Number::Format->new( -mega_suffix => ' MB' ); my $hostname = `hostname`; chomp $hostname; print <<WARN; Warning! Resources are getting low in Sybase database! Threshold used: Log or Data Empty Percent < @{[ sprintf( '%5.2f%%', $OPT_warn_perce +nt * 100 ) ]} Host: $hostname Database: $db_info->{name} Server: $OPT_server Owner: $db_info->{owner} Created: $db_info->{created_on} Total Size: @{[ $x->format_bytes( $db_info->{total_size} ) ]} Data Size: @{[ $x->format_bytes( $db_info->{data_size}, 2 ) ]} Data Used: @{[ $x->format_bytes( $db_info->{data_used}, 2 ) ]} Data Free: @{[ $x->format_bytes( $db_info->{data_free}, 2 ) ]} Empty: @{[ sprintf( '%5.2f%%', $data_empty * 100 ) ]} Log Size: @{[ $x->format_bytes( $db_info->{log_size}, 2 ) ]} Log Used: @{[ $x->format_bytes( $db_info->{log_used}, 2 ) ]} Log Free: @{[ $x->format_bytes( $db_info->{log_free}, 2 ) ]} Empty: @{[ sprintf( '%5.2f%%', $log_empty * 100 ) ]} Please fix the situation ASAP! WARN } exit(0) unless ( $OPT_verbose ); print "Yes, this is a lame excuse for a verbose report:\n\n", Dumper( $db_info ), "\n"; } sub translate_mb_to_bytes { my $text = shift; $text =~ s/\s*$//; if ( $text =~ s/MB$// ) { $text = $text * 1000000; } return $text; } sub usage { return <<USAGE; view_sybase_size - Script to ensure that the data/log devices are not filling up in a Sybase database. Usage: view_sybase_size -U user -P password -D database [ -S server ] [ --verbose ] [ --warn_percent=50 ] [ --sybase /opt/sybase-11.9.2 ] [ --help ] Sample cron usage: # Do a check every night at 2 am -- if everything is ok then nothing # is displayed -- errors get emailed via cron's parameters 0 2 * * * /home/sybase/scripts/view_sybase_size -U www -P www -D mydb However, it's recommended for security reasons that you put the above call into a shell script and call that from cron. Otherwise your login information will be in the subject of the email cron sends to you... Options: --warn_percent (optional) Percentage to use for testing whether to return an error message. If the emptiness either the data or log devices fall below this number, then we display the warning. Example view_sybase_size --warn_percent=60 Would send a warning whenever either the log or data segment is less than 60% empty. The current default set in this script is $DEFAULT_WARN_PERCENT% -S (optional) Database server we should connect to. This is only optional if you have the DSQUERY environment variable set, otherwise DBD::Sybase will not be able to open a connection. -D Database we should display the information for. -U Username for database. -P Password for database. --sybase (optional) Set the $ENV{SYBASE} variable if necessary. --help (optional) Displays this information --verbose (optional) Displays details about the database and its devices. (Currently very crude.) USAGE } __END__ |
|
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
RE: Check Sybase data/log sizes
by cadfael (Friar) on Nov 02, 2000 at 20:36 UTC | |
by lachoy (Parson) on Nov 03, 2000 at 01:55 UTC | |
by cadfael (Friar) on Nov 03, 2000 at 02:39 UTC |