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.

SQL Statement

The definition of the alert can be found in the screenshot below, and here is the SQL statement to plug into that screen.

-- 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'
print @SQL
   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

Alert Definition