#
# Monitor the space consumed by a sql server database  
# Edward G
# 13 Jan 2003
# 

use strict;
use warnings;

sub commafy { # cookbook
     my $text = reverse $_[0];
     $text =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
     return scalar reverse $text;
}

sub getdbname { # assumes exactly one mdf and exactly one ldf    
    my $dbname = $_[0];
    my @cmd = `osql -U*user* -P*pass* -d$dbname -w999 -Q"select filena
+me from sysfiles"`;
    my ($mdf,$ldf) = ('','');
    
    for (@cmd) {
        if (/(\w\:\\.+\.mdf)/i) {
            $mdf=$1;
        }
        if (/(\w\:\\.+\.ldf)/i) {
            $ldf=$1;
        }
    }

    return ($mdf,$ldf);    
}    


MAIN: {

    my $dbname=shift or die "Usage: perl.exe $0 <dbname>\n";

    my ($mdf,$ldf); 

    my $count   =  0;
    my $oldsize = -1;

    while (1) {

        ($mdf,$ldf) = &getdbname($dbname);

        if ($mdf and $ldf) {
        
            my $msize = (stat($mdf))[7];
            my $lsize = (stat($ldf))[7];
            
            my $fsize = $msize + $lsize;
            
            if (not ($fsize == $oldsize)) {
                
                $msize            = &commafy($msize); 
                $lsize            = &commafy($lsize);
                my $display_fsize = &commafy($fsize);
                
                $count=0 if ($oldsize == 0); # reset the ticker if the
+ database suddenly pops into existence
                
                print "$count\tmdf=$msize\tldf=$lsize\ttotal=$display_
+fsize\n";
                
                $oldsize = $fsize;
            }

        } else {
            
                if ($oldsize != 0) {
                    print "*\tmdf= *\tldf= *\ttotal= *\n";
                }
                
                $oldsize = 0;
                
        }
        
        sleep 1;
        $count++;
    }

} # MAIN

exit;

__END__
d:\>perl.exe monitor.pl tempdb
0       mdf=8,388,608   ldf=786,432     total=9,175,040  <-- Initial s
+ize
75      mdf=11,206,656  ldf=786,432     total=11,993,088 <-- Started S
+ELECT INTO #TEST
76      mdf=16,449,536  ldf=786,432     total=17,235,968
77      mdf=21,954,560  ldf=786,432     total=22,740,992
78      mdf=29,294,592  ldf=786,432     total=30,081,024
79      mdf=35,520,512  ldf=786,432     total=36,306,944
80      mdf=43,057,152  ldf=786,432     total=43,843,584
81      mdf=47,382,528  ldf=786,432     total=48,168,960
82      mdf=52,166,656  ldf=786,432     total=52,953,088
83      mdf=57,409,536  ldf=1,048,576   total=58,458,112
84      mdf=69,533,696  ldf=1,048,576   total=70,582,272
85      mdf=76,480,512  ldf=1,048,576   total=77,529,088 <-- Cancelled
+ SELECT
107     mdf=8,388,608   ldf=524,288     total=8,912,896  <-- DBCC SHRI
+NKDATABASE('tempdb',0)
[download]