April 2011 Edition

How to Monitor Disk Space Usage with SQL Anywhere

Monitoring disk space usage as well as being able to react in case of low-disk space scenarios should be on the "to do" list of every database administrator. In order to do that effectively, it's important to understand what files are used by the database server and where they are located.

In general, a SQL Anywhere database consists of 3 types of files:
- A “dbspace” (one or more) where database objects are stored (e.g. tables, indexes, procedures, data). This file typically has an extension of “.db” or “.dbs”
- A transaction log (possibly also a mirror log) where all transactions are stored for database recovery. This file typically has an extension of “.log” or “.mlg”
- Temporary file, which holds all temporary data for the database server (e.g. temporary tables used for sorting, storage for data pages that cannot fit into memory, etc.)

To find out each file’s size and location, SQL Anywhere provides some database properties that can be queried with SQL commands:

E.g.

-- to find out what dbspaces are defined
SELECT file_name FROM sysfile;
-- to get the file size in bytes for 'system' db space
SELECT DB_EXTENDED_PROPERTY( 'FileSize' );
-- size of <db space>; db1.db in this case
SELECT DB_EXTENDED_PROPERTY( 'FileSize', 'db1' );

-- name of transaction log
SELECT DB_PROPERTY( 'LogName');
-- size of transaction log in bytes
SELECT DB_EXTENDED_PROPERTY( 'FileSize', 'translog' );

-- temporary file name :
SELECT DB_PROPERTY( 'TempFileName');
-- directory where temporary file was created
SELECT PROPERTY('TempDir');
-- size of temporary file in bytes
SELECT DB_EXTENDED_PROPERTY( 'FileSize', 'temporary' );

To generate information about how much “free space” is currently reserved in the database:

-- number of free pages in the database
SELECT DB_EXTENDED_PROPERTY( 'FreePages');
-- page size in bytes
SELECT DB_PROPERTY('PAGESIZE');
-- free space on the database in bytes
SELECT DB_EXTENDED_PROPERTY( 'FreePages')*DB_PROPERTY('PAGESIZE');

In addition to database properties, SQL Anywhere provides the ability to create an event that can automatically check the current disk space usage on the system and can be used to generate a report via a stored procedure:

-- create global temp table to store space information

CREATE GLOBAL TEMPORARY TABLE "DBA"."satmp_db_space" (
"ts" TIMESTAMP NOT NULL,
"dbspace_name" CHAR(128) NOT NULL,
"space_in_bytes" NUMERIC(30,0) NULL,
PRIMARY KEY ( "ts" ASC, "dbspace_name" ASC )
) NOT TRANSACTIONAL SHARE BY ALL;

--create a ‘LowDBSpace’ event that will trigger every 300 secs and execute a number of SQL commands

CREATE EVENT "LowDBSpace" TYPE "DBDiskSpace"
WHERE EVENT_CONDITION('DBFreePercent') < 10 AND EVENT_CONDITION('Interval') >= 300
HANDLER
BEGIN
DECLARE ts datetime;
-- set the the time sample was taken
SET ts = NOW();

-- populate satmp_db_space table with file size statistics
INSERT INTO DBA.satmp_db_space (ts,dbspace_name,space_in_bytes)
SELECT ts,dbspace_name, DB_EXTENDED_PROPERTY( 'FileSize',dbspace_name) * DB_PROPERTY('PAGESIZE') tot From sys.sysfile
UNION
SELECT ts,'translog',DB_EXTENDED_PROPERTY( 'FileSize', 'translog' ) * DB_PROPERTY('PAGESIZE') tot
UNION
SELECT ts,'TotalDBSpace',SUM(t.tot) FROM
(
SELECT DB_EXTENDED_PROPERTY( 'FileSize',dbspace_name) * DB_PROPERTY('PAGESIZE') tot From sys.sysfile
UNION
SELECT DB_EXTENDED_PROPERTY( 'FileSize', 'translog' ) * DB_PROPERTY('PAGESIZE') tot
) t
UNION
SELECT ts,'DBFreeSpace', EVENT_PARAMETER('DBFreeSpace') * 1024*1024 ;
COMMIT;

-- store a message in the console log
MESSAGE 'Low disk space Event: Database free disk space: '|| EVENT_PARAMETER( 'DBFreeSpace') || ' MB';

-- send an email to dbadmin with free space information (email needs to be setup first)
-- CALL xp_sendmail( recipient='DBAdmin',subject='Low disk space',"message"='Database free disk space '|| EVENT_PARAMETER( 'DBFreeSpace' ) );
END;

Once the above event is installed and enabled, it is quite easy to report on any database space growth when the file system is nearing capacity. This is helpful when troubleshooting space problems, or trying to estimate future disk space requirements.

This example shows an increase of >200MB in database total size over a thirty minute period.

SELECT ts,dbspace_name,CONVERT(numeric(30,0), space_in_bytes / 1024/1024) as space_in_MB
FROM satmp_db_space
WHERE dbspace_name IN ('DBFreeSpace','TotalDbSpace')
ORDER BY ts ASC;

Ts	dbspace_name	space_in_MB
2011-03-30 13:52:43.578	TotalDBSpace	105
2011-03-30 13:57:44.281	DBFreeSpace	24594
2011-03-30 13:57:44.281	TotalDBSpace	105
2011-03-30 14:02:45.031	DBFreeSpace	24594
2011-03-30 14:02:45.031	TotalDBSpace	105
2011-03-30 14:07:45.750	DBFreeSpace	24493
2011-03-30 14:07:45.750	TotalDBSpace	207
2011-03-30 14:12:46.562	DBFreeSpace	24390
2011-03-30 14:12:46.562	TotalDBSpace	309
2011-03-30 14:17:47.218	DBFreeSpace	24390
2011-03-30 14:17:47.218	TotalDBSpace	309
2011-03-30 14:22:48.062	DBFreeSpace	24390