Using dbcc dbtable() to determine if a database is in LOG SUSPEND

Usually when a database runs out of log and a process enters LOG_SUSPEND, you can see this in the output of sp_who. What happens if the process doesn’t exist? Sometimes, Sybase ASE doesn’t properly clean up after a process exits (this would be a bug) and leaves resources still tied up.

If that particular process filled up the log, you might be able to detect it if you ran Ed Barlow‘s “sp__dbspace” stored procedure. The space structures that sp__dbspace uses may only get updated when the log passes the Last Chance Threshold (LCT). For small databases the LCT might be at 80% or less of the total log or the space usage information could be just plain wrong. If you have a monitoring process that alerts you if the log fills to 90% or higher, you may never get the alert because ASE is still reporting that the log is still 89% full when in fact, it is plum full. No matter how many times you performed dump tran with truncate_only or no_log, the log would never be freed up until you restarted ASE and then performed a dump tran.

If you run dbcc dbtable(< dbname>), you can look for the “dbt_logsuspended” boolean in the output. Note, that dbt_logsuspended will only be set to ‘1’ if there is an active or phantom process that is in LOG SUSPEND.

Obviously, checking if the database is in LOG SUSPEND is just one piece of determining if there is an outage for a particular database. In the coming days, I’ll provide other methods to complete it.

I am including a perl script that shows the use of dbcc dbtable().

#!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;
    use File::Basename;

    our $SYBDBA1_login = ’sa’;
    our $SYBDBA1_password = ‘password’;
    our %dbTable;

    sub _syb_errhandler_dbTable{
        my ($err, $sev, $state, $line, $server, $proc, $msg, $sql, $err_type) = @_;
        if ($msg =~ m/dbt_logsuspended=(\d+)/) {
            $dbTable{’log suspended’} = $1;
        }
        return 0;
    }

    sub _is_db_in_logsuspend {
        my ($dbh, $dbname) = @_;
        # dbcc ouput is sent to STDERR, so we redirect it to /dev/null just for this subroutine 
        local *STDERR;
        open (STDERR, ">", "/dev/null");
        # set up temporary error handler.  dbcc commands
        #  can’t be caught using $dbh->err*
        $dbh->{syb_err_handler} = \&_syb_errhandler_dbTable;
        $dbh->do("dbcc traceon(3604)");
        $dbh->do("dbcc dbtable($dbname)");
        $dbh->{syb_err_handler} = undef;
        return $dbTable{’log suspended’};
    }

    sub _connect_dbms {
        my $loc_dbh;
        my $script = basename($0);
        # we use the new keepalive parameter to enable TCP_KEEPALIVE for this script
        if ($loc_dbh = DBI->connect("dbi:Sybase:server=DBADEV1;loginTimeout=10;timeout=30;keepalive=1;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1", $SYBDBA1_login, $SYBDBA1_password, { PrintError => 0, RaiseError => 0 } )) {
             return $loc_dbh;
        } else {
             report_err("unable to connect to DBADEV1", "error");
        }
    }

    my $dbh = _connect_dbms;
    my $dbname = "test";

    if (_is_db_in_logsuspend($dbh, $dbname) == 1) {
            print "Database $dbname is in LOG SUSPEND.\n";
    } else {
            print "Database $dbname is okay.\n";
    }
Share Button