My database is full

Owned by Reinhard Mayr (Unlicensed)
Last updated: Jul 06, 2017 by Wolfgang Granzer
2 min read

The BMS Server 2.0 allows historical data recording on a Microsoft SQL Server database. You can either use the Microsoft SQL Server Express instance that is installed along with the BMS Server 2.0 (named NETX_SERVER_SQL) or connect to any other Microsoft SQL Server instance locally or remotely. Use the config parameters in the BMS Studio under Server / System configuration / XDB.

The Microsoft SQL Server Express edition allows up to 10 GB database size (corresponding to roughly 90 million records), exceeding that size is not possible, even if your disk has space left.

When the BMS Server starts the workspace it checks if the database of the name NETX_<workspace-name> already exists. If that is not the case the database will be created automatically.

Prevention

In order to avoid the database grow full too quickly, consider following points:

  • Use as few historical datapoints as possible.
  • Periodically polled datapoints flagged as historical will lead to a constant growth of the database because each read value creates a record in the database. Make estimate of the number of records per day just by multiplication, and sum up these numbers for each datapoint. Compare the result with the rough limit of 90 million records to get an estimate of number of days fitting in the database.

Automatic Deletion of Records

The BMS Server 2.0 provides some means to monitor and control the database size:

  • In the BMS Server's item tree there is NETx\Server\Database\Size indicating the current size in MB. You might want to observe that value or even use it as a trigger for an event to notify the user.
  • In the BMS Studio under Server / System configuration / XDB / Max data age defines the number of days the historical records should be kept in the database. Older records will be deleted automatically.
  • In the BMS Studio, if the Server / System configuration / XDB / Auto-purge near-full database option is activated, the BMS Server deletes the oldest 20 % of the data if the database is 90 % full. This applies only to SQL Server Express editions!

Manual Deletion of Records

In some cases the automatic deletion of records fails because the process of deletion takes longer than the command timeout as configured in Server / System configuration / XDB / Command timeout. A manual deletion of old records can solve the problem.

Open a shell (cmd.exe) and enter

osql -S .\NETX_SERVER_SQL -U sa -P netxnetx_123456


Substitute instance name, username, and password according to your SQL Server setup.

This opens the SQL Server console. Substitute <workspace-name> and <maxDataAge> as in your workspace and enter the delete query followed by the go command:

1> DELETE FROM NETX_<workspace-name>.dbo.NETX_HISTORICAL_VALUE WHERE Date < (GETDATE()-<maxDataAge>)
2> go

The deletion completed when a message like this appears:

(<nn> rows affected)

In large databases such a process can take an hour or more, so please be patient about it.

As an alternative, you can delete the oldest data on a percentage base. Substitute <workspace-name> and <percentage> as in your workspace and enter the delete query followed by the go command:

1> DELETE FROM NETX_<workspace-name>.dbo.NETX_HISTORICAL_VALUE WHERE LOCAL_DATE IN(SELECT TOP(<percentage>) PERCENT LOCAL_DATE FROM NETX_<workspace-name>.dbo.NETX_HISTORICAL_VALUE ORDER BY LOCAL_DATE ASC)
2> go