Custom Ignite Alert: Sybase Database Freespace#LR14353
This custom Ignite alert replaces the canned Ignite Database Freespace alert for Sybase and is improved in two ways:
- The canned alert will error if there are any databases that are no online when it executes. This new alert will skip those databases.
- This alert will report on each segment inside the database to give more specific information about what is filling up.
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
Ignite alert definition