Knowledge Base

This article provides a method of estimating the SQL Server database size when using PcVue and the HDS to record Trend and / or Log data.Why only an estimate?
1. Many of the properties that can be recorded are character based and of variable length. We know the maximum size for each property but the actual size will depend on project configuration.
2. This is a simplified calculation, the actual calculation is far more complex and outside the scope of this article.This article mainly concerns the database (.mdf) file itself. The process of recording data in a database also generates another file called the Transaction Log (.ldf) File. There are some notes about this file at the end of this article.

Some of the properties that are recorded by PcVue are mandatory, others are optional. For information about all the available properties, the data type for each property and the maximum size see the main help:

The Application Explorer.Archives.Configuring databases.Configuring the database tables.What properties can you record

Trend Table

The Trend table has three elements that use storage.
1. The columns in which the data is recorded. Mandatory properties plus any optional properties.
2. A primary index (type – non clustered). Mandatory properties only.
3. A clustered index (type clustered). Chrono only.

Each record requires an entry in each of these three elements. Therefore 1 record requires storage for 2 x mandatory properties + optional properties + chrono.

The mandatory properties for the Trend Table are:

Example 1
20 variables recorded at 1 second, 100 variables recorded at 10 seconds and 1000 variables recorded at 60 seconds. We will assume that the “average” tagname is 40 characters. Mandatory properties only recorded. The space required for 1 record is (8+40+8+2)x2 +8 = 124 bytes.

Example 2
20 variables recorded at 5 seconds, 100 variables recorded at 10 seconds and 1000 variables recorded at 60 seconds. We will assume that the “average” tagname is 40 characters. Mandatory properties plus one Text Attribute (Varchar, 0 to 100 bytes). The space required for 1 record is (8+40+8+2)x2 +100 +8 = 224 bytes.

Log Table

Estimating the space required by a Log Table is more difficult.

The Log Table has two elements that use storage.
1. The columns in which the data is recorded. Mandatory properties plus other (non optional) properties, plus optional properties.
2. A primary index (type clustered). Mandatory properties only.

Each record requires an entry in each of these two elements.
Therefore 1 record requires storage for 2 x mandatory properties + other (non optional) properties + optional properties

The mandatory properties for the Log Table are:

The non-optional properties for the Log Table are:

As many of the properties are of Varchar type and of variable size, it will be much more difficult to estimate the space required for each record. Also, events by their nature, are spontaneously recorded as they occour. So you also need some idea of the frequency. At best you can probably only calculate a best guess or a worst case scenario.

About the Transaction Log File (.ldf)

The Transaction Log File (.ldf) is used to record transactions that have taken place on a database. Its purpose is to allow a database to be recovered to a certain point in time, but to do this you also need the data so you can imagine it might not be that useful in the case of PcVue. The .ldf file can grow very large if it is not managed.

The .ldf file has two recovery models known as Full and Simple. The Simple model is recomended for PcVue as it uses much less storage space. From PcVue 11.2 onwards the Simple model is forced whenever PcVue creates a database. Prior to 11.2 the recovery model used was inherited from the SQL Server Model Database which itself depends on the SQL Server instalation.

For more information see the MicroSoft technical article: https://technet.microsoft.com/en-us/library/ms175987(v=sql.105).aspx