Page tree
Skip to end of metadata
Go to start of metadata

Purpose

This purpose of this wiki is to show you how to monitor how much disk space SQL Anywhere is using on your system.

Overview

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.

Prerequisite

 Must have the MANAGE ANY DBSPACE system privilege (on SQL Anywhere 16).

Monitoring Disk Space

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. Using the SQL Anywhere 16 Demo database. (See: http://dcx.sap.com/index.html#sa160/en/saintro/fg-sademo.html )

-- create dbspace "dbspace1"
CREATE DBSPACE "dbspace1" AS 'dbspace1.db';

-- preallocate 2MB
ALTER DBSPACE "dbspace1" ADD 2 MB;

-- 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>; dbspace1.db in this case
SELECT DB_EXTENDED_PROPERTY( 'FileSize', 'dbspace1' );

-- 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 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 disk 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 time as of when 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 'Database Free Disk Space: '|| EVENT_PARAMETER( 'DBFreeSpace')
|| ' MB (' || convert(numeric(10,2),(EVENT_PARAMETER( 'DBFreeSpace')/1024)) || ' GB)';
-- 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 on a database, it is easy to query the table to generate a 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;
Tsdbspace_namespace_in_MB
2011-03-30 13:52:43.578TotalDBSpace105
2011-03-30 13:57:44.281DBFreeSpace24594
2011-03-30 13:57:44.281TotalDBSpace105
2011-03-30 14:02:45.031DBFreeSpace24594
2011-03-30 14:02:45.031TotalDBSpace105
2011-03-30 14:07:45.750DBFreeSpace24493
2011-03-30 14:07:45.750TotalDBSpace207
2011-03-30 14:12:46.562DBFreeSpace24390
2011-03-30 14:12:46.562TotalDBSpace309
2011-03-30 14:17:47.218DBFreeSpace24390
2011-03-30 14:17:47.218TotalDBSpace309
2011-03-30 14:22:48.062DBFreeSpace24390

How to Send an E-mail via SMTP

In order to send an e-mail to alert a database administrator instead, an administrator can use the xp_startsmtp, xp_sendmail, and xp_stopsmtp commands to connect to an SMTP server in the SQL Anywhere event. For more information on how to accomplish this, see: http://scn.sap.com/community/sql-anywhere/blog/2010/01/15/using-sql-anywhere-innsbruck-to-send-emails-through-gmail.

Note: If you're using Google GMail to send e-mail over Secure SMTP, you can download GMail's public certificate from GeoTrust (certificate link).

CREATE EVENT "LowDBSpace" TYPE "DBDiskSpace"
  WHERE EVENT_CONDITION('DBFreePercent') < 10 AND EVENT_CONDITION('Interval') >= 300
  HANDLER
BEGIN
CALL xp_startsmtp('john.doe@gmail.com', 'smtp.gmail.com', 587, 60, 'John Doe','john.doe@gmail.com', 'itsasecret', 'C:\certs\Equifax_Secure_Certificate_Authority.pem'); 
CALL xp_sendmail( recipient='john.doe@gmail.com',subject='Low disk space',"message"='Database Free Disk Space '|| EVENT_PARAMETER( 'DBFreeSpace' ) );
CALL xp_stopsmtp();
END;

 

 

  • No labels