DESCRIPTION
This 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.
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
