Is there a table that stores information as to when sp_dbxt_extend_db fires?
I am setting up an ASE 15.7 database with auto-expansion enabled. I have the growby rates and thresholds configured. When the auto-expansion detects it's time to expand the device, we clearly see information about the expansion in the ASE log file. Information in the log file is similar to:
00:0002:00000:00024:2015/08/20 12:03:09.98 server background task message: Threshold action procedure 'sp_dbxt_extend_db' fired in db 'QIP' on segment 'infraaudit2015seg'. Space left: 640 logical pages ('10M').
00:0002:00000:00024:2015/08/20 12:03:09.98 server background task message: DISK RESIZE name = 'qip_infra_partition1', size = '4.0M' -- Db: QIP Segment: infraaudit2015seg
00:0004:00000:00000:2015/08/20 12:03:09.98 kernel Performing space allocation for device '/opt/sybase/data/qip_infra_partition1' (0.00 Gb). This may take some time.
00:0002:00000:00024:2015/08/20 12:03:10.00 kernel Finished initialization.
00:0002:00000:00024:2015/08/20 12:03:10.01 server background task message: sp_dbxt_do_resize_dev: Device qip_infra_partition1 of size 24M resized by 4M to a total size of 28M.
00:0002:00000:00024:2015/08/20 12:03:10.01 server background task message: ALTER DATABASE QIP on qip_infra_partition1 = '4.0M' -- Segment: infraaudit2015seg
00:0002:00000:00024:2015/08/20 12:03:10.01 server Extending database by 256 pages (4.0 megabytes) on disk qip_infra_partition1
00:0002:00000:00024:2015/08/20 12:03:10.03 server background task message: Database 'QIP' was altered by total size '4M' for segment 'infraaudit2015seg'.
Can I query this information in the database? Does sp_dbxt_extend_db write any of this data to a table? I would like to expose some of this data to a WEB/UI interface, and would prefer a query, rather than parsing a log file.
Kevin Sherlock replied
Not sure that the stored proc does any type of self auditing to a table. Easy enough to look at the source code for the proc.
BUT, keep in mind that you have the master..sysusages table to refer to here when device fragments are added to a database. There is a "crdate" column that defines the time when a fragment was added to your database. In your case query:
select db_name(u.dbid) as "DBName"
,d.name as "DevName"
,u.size * @@maxpagesize / 1024.00 / 1024.00 as "SizeMb"
,u.segmap -- map this to a segment name if desired
from master..sysusages u
ON u.vdevno = d.vdevno
and d.status & 2 = 2
where u.dbid = db_id('QIP')
order by u.dbid,u.lstart