Database growth

Overview

Volumetric analysis is the process of examining a database and determining the type of data stored and its expected rate of growth. This information is useful when choosing the hardware on which the database will be hosted. Volumetric analysis should always take into consideration the complete database including all indexes, both clustered and unclustered. The final projections outlined here are liberal estimates only, which are based on certain technical and usage assumptions.

While volumetric analysis is an important part in determining the size of the database, many other factors are needed to be considered to optimize database performance. These include Processor speed, memory, hard-drive speed and fragmentation, RAID configurations, transaction logging, table and query design, indexing, network capabilities, memory tuning, user security and others.

Technical Assumptions

The projections listed below are based on the usage of a SQL Server database hosting the HelpMaster SQL Server database as created via the default database scripts. This database is fully indexed, normalized and contains full referential integrity constraints. Modifying the base database in any way will affect the final projections.

Population Assumptions

All variable and fixed length string settings have been calculated to assume a 30% fill factor. This means that a database string field that is allocated to store 200 characters will be calculated under the premise that when populated it will only contain 60 characters. For this reason, the total database size calculation is only an indication only. In many instances, a fill factor of 30% will be very generous, especially for text fields.

Usage Assumptions

All database size and index calculations have been determined using the following parameters.

1. Client register of 5000 clients, each of which has 1 phone number and 1 email address

2. Site register of 500 sites, each of which has 1 phone number and 1 email address

3. 1000 addresses to be divided between the sites and clients

4. Asset register of 5000 assets, each of which has 1 user-defined value

5. Staff register of 20 staff

6. 200 Issues

7. 200 System codes

8. 100 Knowledge base articles

9. 9125 jobs logged per year which equates to logging 25 jobs per day over a 365 day year

   • Each job has the following entities linked to it :
     • 1 client
     • 1 asset
     • 1 attachment (note attachments are not stored in the database. These are stored as files in a folder on your fileserver)
     • 5 action log entries, each of which has a saved outgoing email

10. 50 Job templates

11. 50 Action templates

12. 50 Email templates

13. 100 triggers either set up as Priority Manager profiles or personal reminders

These usage parameters are indicative of a real-world example where asset management, outgoing email, attachments and template usage are employed throughout the helpdesk operation.

Total Database size

Using the assumptions stated above, the projected size of such a database after 1 year would be approximately 139 megabytes. This includes both base table size and indexes, but does not include the database transaction log, nor the Full Text Search indexes as discussed below.

Transaction Log Size

Once the database table size has been estimated, the transaction log needs to be considered. The size and nature of the transaction log should be set by the database administrator according to the needs of the business. The size of the transaction log is generally set as a percentage of the database size and is primarily based on the expected transaction level and the rate that transaction logs are saved to either disk or tape storage.

Note! The size of a database transaction log can grown very large if the database backup is not performed regularly. Switching the database into “simple” backup mode and then performing a full backup should reduce the transaction log size. Please read the Microsoft SQL documentation for backup types and their effect on the transaction log.

For further information on configuring your HelpMaster database transaction log, please refer to the database administrator, or the Microsoft SQL Server Books On Line (BOL) reference.

Full Text Search Index Size

When a database utilizes the full text search indexes, significant growth may occur in the size of the database files and logs.

Projected future growth

Based on the assumptions above, and further assuming that the clients, sites and asset metrics remain unchanged, the only significant growth in the database will be due to the logging of more jobs.

Based on logging a further 9125 helpdesk jobs per annum (25 per day), the database can be expected to grow by approximately 117 megabytes per year. The table below displays the database growth over 5 years, with a 30% size transaction log.

Other things that affect database size

Event Logs

In addition to the normal entering of data and logging jobs, database size is also affected by the logging options that have been configured within HelpMaster.

HelpMaster has logging options for:

• System and Logon Event Logs

• Email Manager Logs

• Priority Manager Logs

• Active Directory Logs

• Triggered Event Logs.

Each one of these log files can be configured to change the behaviour and size of the logs.

Database Maintenance and Backup options

Regular database maintenance and backup is vital for SQL Server databases to perform at their peak level. Over time, database indexes, temporary tables, log files, and other objects can cause the overall database size to grow.

Ensure that the database is regularly maintained, and backed-up. This will help in reducing the size of the database.

Further information

For further information about the information contained in this volumetric analysis for the HelpMaster SQL Server database, please contact PRD Software.

See Also

Hardware and Software Requirements - Microsoft SQL Server

Database and Reports Wizard

Database maintenance