on 10-27-2015 3:15 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.