cancel
Showing results for 
Search instead for 
Did you mean: 

Is there a table that stores information as to when sp_dbxt_extend_db fires?

lisa_sayre
Explorer
0 Kudos

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.

Thanks!

Lisa Sayre

Accepted Solutions (1)

Accepted Solutions (1)

kevin_sherlock
Contributor
0 Kudos

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

      ,u.segmap  -- map this to a segment name if desired

      ,u.crdate

from master..sysusages u

    INNER JOIN

    master..sysdevices d

    ON u.vdevno = d.vdevno

        and d.status & 2 = 2

where u.dbid = db_id('QIP')

order by u.dbid,u.lstart

kevin_sherlock
Contributor
0 Kudos

Forgot to address the other end of the question about device resizing.  In a similar vein, there is also the
"crdate", and "resizedate" colums in the master..sysdevices table that you could apply to the above query.

lisa_sayre
Explorer
0 Kudos

Hi Kevin,

Thank you for your quick response and useful information.  I looked at the stored procedure code, as per your suggestion, and it appears that just temporary tables are used to store the data that is used to process auto expansion.  However, we could probably use the query suggestions that you provided to derive when an expansion took place, and how much space was added based on the expansion policies.  Thanks again - greatly appreciated!!

Lisa

Answers (0)