Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > Tips > SQL Anywhere INSIDER April 2011  
RSS Feed
 
 
 

 

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

 


 

DOCUMENT ATTRIBUTES
Last Revised: Apr 13, 2011
Product: SQL Anywhere
Technical Topics: SQL Anywhere
  
Business or Technical: Technical
Content Id: 1092812
Infotype: Tips
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics