Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Sybase database space usage

by mpeppler (Vicar)
on Dec 08, 2001 at 00:28 UTC ( #130299=sourcecode: print w/replies, xml ) Need Help??
Category: Database Admin
Author/Contact Info Michael Peppler, mpeppler@peppler.org
Description: This script checks the available space in a Sybase database, and lists space usage for each table in the database.

Usage is:

check-space.pl -U <user> -P <pwd> -S <server> -D <database>
Output looks something like this:
plum/excalibur spaceusage report Database size: 500.00 MB Log size: 50.00 MB Free Log: 26.15 MB Reserved: 353.54 MB Data: 253.71 MB Indexes: 96.42 MB Free space: 29.29 % Table information (in MB): Table Rows Reserved Data Indexes give_up_refs 104112 1.97 1.92 0.04 gup_instruction 96 0.03 0.00 0.00 ledger 152213 25.90 14.83 11.03 market 1491304 125.22 101.28 23.54 market_perpetua 342694 25.69 25.23 0.39 mkt_close 57214 1.73 1.68 0.04
#!/usr/bin/perl -w

use strict;
use DBI;

use Getopt::Long;

my %args;

GetOptions(\%args, '-U=s', '-P=s', '-S=s', '-D=s');

my $dbh = DBI->connect("dbi:Sybase:server=$args{S};database=$args{D}",
+ $args{U}, $args{P});

$dbh->{syb_do_proc_status} = 1;

my $dbinfo;

# First check space in the DB:
my $sth = $dbh->prepare("sp_spaceused");
$sth->execute;
do {
    while(my $d = $sth->fetch) {
    if($d->[0] =~ /$args{D}/) {
        $d->[1] =~ s/[^\d.]//g;
        $dbinfo->{size} = $d->[1];
    } else {
        foreach (@$d) {
        s/\D//g;
        }
        $dbinfo->{reserved} = $d->[0] / 1024;
        $dbinfo->{data} = $d->[1] / 1024;
        $dbinfo->{index} = $d->[2] / 1024;
    }
    }
} while($sth->{syb_more_results});

# Get the actual device usage from sp_helpdb to get the free log space
$sth = $dbh->prepare("sp_helpdb $args{D}");
$sth->execute;
do {
    while(my $d = $sth->fetch) {
    if($d->[2] && $d->[2] =~ /log only/) {
        $d->[1] =~ s/[^\d\.]//g;
        $dbinfo->{log} += $d->[1];
    }
    if($d->[0] =~ /log only .* (\d+)/) {
        $dbinfo->{logfree} = $1 / 1024;
    }
    }
} while($sth->{syb_more_results});

$dbinfo->{size} -= $dbinfo->{log};

my $freepct = ($dbinfo->{size} - $dbinfo->{reserved}) / $dbinfo->{size
+};

print "$args{S}/$args{D} spaceusage report\n\n";
printf "Database size: %10.2f MB\n", $dbinfo->{size};
printf "Log size:      %10.2f MB\n", $dbinfo->{log};
printf "Free Log:      %10.2f MB\n", $dbinfo->{logfree}; 
printf "Reserved:      %10.2f MB\n", $dbinfo->{reserved};
printf "Data:          %10.2f MB\n", $dbinfo->{data};
printf "Indexes:       %10.2f MB\n", $dbinfo->{index};
printf "Free space:    %10.2f %%\n", $freepct * 100;

if($freepct < .25) {
    printf "**WARNING**: Free space is below 25%% (%.2f%%)\n\n", $free
+pct * 100;
}

print "\nTable information (in MB):\n\n";
printf "%15s %15s %10s %10s %10s\n\n", "Table", "Rows", "Reserved", "D
+ata", "Indexes";

my @tables = getTables($dbh);

foreach (@tables) {
    my $sth = $dbh->prepare("sp_spaceused $_");
    $sth->execute;
    do {
    while(my $d = $sth->fetch) {
        foreach (@$d) {
        s/KB//;
        s/\s//g;
        }
        printf("%15.15s %15d %10.2f %10.2f %10.2f\n",
           $d->[0], $d->[1], $d->[2] / 1024, $d->[3] / 1024,
           $d->[4] / 1024);
    }
    } while($sth->{syb_more_results});
}


sub getTables {
    my $dbh = shift;

    my $sth = $dbh->table_info;
    my @tables;
    do {
    while(my $d = $sth->fetch) {
        push(@tables, $d->[2]) unless $d->[3] =~ /SYSTEM|VIEW/;
    }
    } while($sth->{syb_more_results});

    @tables;
}
Replies are listed 'Best First'.
Re: Sybase database space usage
by busunsl (Vicar) on Dec 13, 2001 at 14:14 UTC
    Nice!

    Here is a slightly patched version that gets the free log space correct for versions prior to 12.0

    #!/usr/bin/perl -w use strict; use DBI; use Getopt::Long; my %args; GetOptions(\%args, '-U=s', '-P=s', '-S=s', '-D=s'); my $dbh = DBI->connect("dbi:Sybase:server=$args{S};database=$args{D}", + $args{U}, $args{P}); $dbh->{syb_do_proc_status} = 1; my $dbinfo; # First check space in the DB: my $sth = $dbh->prepare("sp_spaceused"); $sth->execute; do { while(my $d = $sth->fetch) { if($d->[0] =~ /$args{D}/) { $d->[1] =~ s/[^\d.]//g; $dbinfo->{size} = $d->[1]; } else { foreach (@$d) { s/\D//g; } $dbinfo->{reserved} = $d->[0] / 1024; $dbinfo->{data} = $d->[1] / 1024; $dbinfo->{index} = $d->[2] / 1024; } } } while($sth->{syb_more_results}); # Get the actual device usage from sp_helpdb to get the free log space $sth = $dbh->prepare("sp_helpdb $args{D}"); $sth->execute; do { while(my $d = $sth->fetch) { if($d->[2] && $d->[2] =~ /log only/) { $d->[1] =~ s/[^\d\.]//g; $dbinfo->{log} += $d->[1]; my ($logfree) = $d->[3] =~ /(\d+)/; $dbinfo->{logfree} += $logfree / 1024; } if($d->[0] =~ /log only .* (\d+)/) { $dbinfo->{logfree} = $1 / 1024; } } } while($sth->{syb_more_results}); $dbinfo->{size} -= $dbinfo->{log}; my $freepct = ($dbinfo->{size} - $dbinfo->{reserved}) / $dbinfo->{size +}; print "$args{S}/$args{D} spaceusage report\n\n"; printf "Database size: %10.2f MB\n", $dbinfo->{size}; printf "Log size: %10.2f MB\n", $dbinfo->{log}; printf "Free Log: %10.2f MB\n", $dbinfo->{logfree}; printf "Reserved: %10.2f MB\n", $dbinfo->{reserved}; printf "Data: %10.2f MB\n", $dbinfo->{data}; printf "Indexes: %10.2f MB\n", $dbinfo->{index}; printf "Free space: %10.2f %%\n", $freepct * 100; if($freepct < .25) { printf "**WARNING**: Free space is below 25%% (%.2f%%)\n\n", $free +pct * 100; } print "\nTable information (in MB):\n\n"; printf "%15s %15s %10s %10s %10s\n\n", "Table", "Rows", "Reserved", "D +ata", "Indexes"; my @tables = getTables($dbh); foreach (@tables) { my $sth = $dbh->prepare("sp_spaceused $_"); $sth->execute; do { while(my $d = $sth->fetch) { foreach (@$d) { s/KB//; s/\s//g; } printf("%15.15s %15d %10.2f %10.2f %10.2f\n", $d->[0], $d->[1], $d->[2] / 1024, $d->[3] / 1024, $d->[4] / 1024); } } while($sth->{syb_more_results}); } sub getTables { my $dbh = shift; my $sth = $dbh->table_info; my @tables; do { while(my $d = $sth->fetch) { push(@tables, $d->[2]) unless $d->[3] =~ /SYSTEM|VIEW/; } } while($sth->{syb_more_results}); @tables; }
      Thanks. I'd only used this with 12.x servers...

      Michael

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://130299]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (7)
As of 2021-01-22 13:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?