Alarm on Database Errors

Ignition
,
1

I have a database that filled up last week. The disk hand plenty of space so I've since expanded it but it may happen again.

I want to be able to generate an alarm if the database is full, or if tag history has other issues.

I see some database system tags in the tag browser, but I'm unsure which one(s) to check, or if there is some other way I should be doing it.

Is there more detail available on these tags in the manual?

image
image426×139 6.83 KB

TIA

2

I haven't seen anything to detect this as far as a tag on Ignition. Even when the drive is full the database connection would still be alive and well. Remember, the database can never get full, it's the drive that runs out of space. You may need to have something on the database side that checks for drive space.

3

If you are on windows, you might be able to use system.util.execute and wmic

Monitor CPU load of DB Server - Ignition - Inductive Automation Forum

4

@PGriffith

Any insight on this?

Maybe I could script a periodic query of the database to check the status and write to a tag if it's got an issue?

5

The system tags are what we have to give you. If the disk is full, it's up to the DB to report that to clients (like Ignition) by failing incoming connections. If it doesn't do that, there's not a lot you can do inside of Ignition.

Maybe you can come up with some kind of query you can run periodically that reports from inside the DB, but it'll be specific to whatever your flavor of DB is.

6

I found this query that might help with your task

select 
    table_schema, sum((data_length+index_length)/1024/1024) AS MB 
from 
    information_schema.tables 
group by 1
2 Likes
7

I'm not an expert in SQL. How would I use this with the default tag history tables?

8

I saw your connection was named MySQL, so that query should give you every table size in a MySQL/MariaDB schema. If you are pointing your tag historian to your MySQL instance, it should include it.

9

That was just a generic name, it's actually an MSSQL database

10

I don't know much about MSSQL, you'll have to refer to google
How To Check Disk Space in SQL Server (mssqltips.com)

1 Like
11

I know some of these are partitions, event data, alarm data, etc. Which of these tables is the "active" tag history data table? (the one that I would need to check to see if the disk is full or not).

image
image244×484 4.38 KB

12

Would it be better to enable/setup data pruning on your history provider? You should be able to estimate the storage needed from your history Prune Age.

13

Technically, the answer is "whichever table is identified by a row in sqlth_partitions where sqlth_partitions.start_time < CURRENT_TIMESTAMP() <= sqlth_partitions.end_time".

Practically, the answer is "whichever table has the right drvid (the _1_ in your screenshot) and the higher calendar index" until/unless you change your partitioning scheme.

14

What do you think about a script like so?

  • Run an update query to insert a row.
  • If the update is successful, run another update query to remove the inserted row.
  • If the update is unsuccessful, write to an alarm tag.
  • Reset button clears the alarm tag.

Would that first update query throw an error if the database was full?

Or, is there a way to check store and forward engine statistics/diagnostics?