Version 1


    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.



    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


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

                            into existing table #seginfo

                            from (

                               select  segn    =,

                                            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 a'

       exec (@SQL)

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


    -- 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