Skip to Content

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.

Thanks!

Lisa Sayre

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 27, 2015 at 09:11 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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