Database disk space considerations

The volume of transactions that occur on a daily basis at any one site should be considered when estimating the amount of disk space that will be required for the databases.

Tables that hold the system configuration, items, processes, bills of material, and so on can be considered static after doing the initial setup and load of process-related data.

Tables that hold work orders, jobs, labor, production, consumption, and so on can be considered dynamic in that they will continue to grow based on the number of transaction that occur on a daily basis.

To determine initial and growth requirements for disk space:

• Estimate the amount of data required to build a system for production go-live.

• Estimate the amount of daily transactions.

Consideration should be given to the number of days that historical job data is required to remain in the database. With archiving implemented, closed work orders and all related data can be moved to an off-line database for reporting purposes. Archiving will ensure that the performance of the production system does not decline over time due to a buildup of historical data in the Production database.

Instead of trying to plug all these factors into an equation, it is recommended that you start with a moderate-size system (say 300 GB of free space) and monitor the data growth. A typical setup would have one year of on-line data in the MES database.

After you have created or migrated the database, it is recommended that you adjust the file sizes. Observe the following guidelines when adjusting the file sizes:

• Large files should be created when the disk is clean so that the file is stored on contiguous sectors.

• Choose an initial file size that is the expected size when the database is fully loaded. For example, if the expected size when fully loaded is 200 GB, then set the initial size to 200 GB.

• For the default 10% growth, you might consider changing to an absolute growth of, say, 1024 MB.

• Make sure that these files are on your Data drive; they should never be on the C: drive.

The following table shows an example of the MES database file sizes for SQL Server.

This setup uses about 150 GB (160 GB for Full Recovery Mode) of disk space. You can always use the Disk Usage report provided within SQL Server Management Studio to see the actual space used.

You must also implement a database maintenance plan that includes re‑indexing, truncation of transaction logs, and shrinking of files.