Version 1

    DESCRIPTION

    This alert replaces the canned Ignite Database Freespace alert for Sybase and is improved in two ways:

     

    1. The canned alert will error if there are any databases that are no online when it executes. This new alert will skip those databases.
    2. This alert will report on each segment inside the database to give more specific information about what is filling up.

     

    ALERT DEFINITION

    To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

     

    Alert Name: Sybase Database Freespace

     

    Execution Interval: 10 minutes

     

    Notification Text: The following databases are running low on free space

     

    SQL Statement:

    -- perform some cleanup in case the last exec of this failed for some reason

    if object_id('#dbs') is not null

       drop table #dbs

     

    if object_id('#seginfo') is not null

       drop table #seginfo

     

    -- save a list of databases that are online

    select name

    into   #dbs

    from   master.dbo.sysdatabases d

    where  d.status & 4384 = 0   -- don't recover, not recovered, single user

    and  d.status2 & 16 = 0   -- offline

     

    create index dbs_pk on #dbs (name)

     

    -- table to hold dbname and segment name (the db_seg column) and the free space pct

    create table #seginfo (db_seg varchar(255) null, pct_free float null)

     

    declare @SQL varchar(1000), @DBName sysname

    select @DBName = min(name) from #dbs

     

    -- loop through each database and get the free space info by segment

    while @DBName is not null

    begin

       set @SQL = 'select ''Database: ''+@DBName+'' - Segment: ''+segn, 100.0 * free / size

                            into existing table #seginfo

                            from (

                               select  segn    = s.name,

                                            size    = sum(u.size),

                                            free    = sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs))

                               from    master.dbo.sysusages u,

                                        '+@DBName+'.dbo.syssegments  s

                               where   (u.segmap & power(2, s.segment)) = power(2, s.segment)

                               and   u.segmap != 4  -- logsegment

                               and  u.dbid = db_id(@DBName)

                               group by s.name) a'

       exec (@SQL)

       select @DBName = min(name) from #dbs where name > @DBName

    end

    -- return the list of dbs, segments and free space back to Ignite for comparison with defined thresholds

    select * from #seginfo

    drop table #dbs

    drop table #seginfo


    Execute Against: Monitored Instance

     

    Units: % Free

     

    High Threshold: Min 0, Max 5

    Medium Threshold: Min 5, Max 10