I was wondering. We recently had our SQL server crumble. Or at least the databases we were working with on your software. What happened was that the log file became so large and took up the drive where SQL was on that SQL would no longer function. The size of the file was 30gig. Not sure why it was so large. But anyway since FSQL should be saving the information locally because the SQL is down, this will give us an opportunity to find out how much information it is storing and if it was the reason or a major contributor to the over fill of the SQL log. So if you could provide a name and location where these files are so we can see how much an 8 hour shift does, it would be thankful.
Carl, Yes when I mentioned SQL I am refering to Microsoft SQL 2005. And I read thru this article and it does seem to be what I am experiencing. However since I do not have System Admin rights I have to wait till monday to change the settings. I am going to present these facts to them as well. But I still have the following questions:
Where does FSQL store the data and what is the file name so I can see how that file grew before we reestablish connection with Microsoft SQL?
Now with these answers I am wornder, does the store and forward section of FSQL have the same issues as this? Will this locally stored file also grow like Microsoft SQL?
With your knowledge of both SQL and your product. If someone deletes the Transaction Log file in Microsoft SQL, does this also delete the tables? Just curious because that is what seems to have happened when our IT staff did a remote access and deleted the log file then all the tables disappeared. However the instance name was still present.
[quote=“Carl.Gould”]When you say “SQL” are you talking about Microsoft SQL Server? It sounds like you need to read this thread: inductiveautomation.com/foru … .php?t=679[/quote]
You'll need to get the proper permissions or work with IT. You're looking for rapidly updating groups that are set to "bi-directional (either)" or "OPC->db". FactorySQL only writes to the database on a value change so precise floats that change by subtle amounts are your likely culprit. Setting a deadband can significantly decrease the number of writes. "Logging groups" (OPC->DB, set to INSERT a new record every time) tend to not be that bad. You know exactly how frequently they run queries.
SQL Server has a feature that stores a transaction log for "rollback" purposes. This involves storing every single query that was run on the database. That might be important if each query was a $$ transaction, but is just wasting resources in this case. Just tell IT that your application is running frequent UPDATE queries and that you don't need to keep a log - they'll understand that.
FactorySQL stores data in whatever tables the group is set to write to. It's simple. A group runs an UPDATE query every update rate, provided that a value changes and the trigger condition doesn't prevent it.
The FactorySQL caching mechanism stores the queries that it can't run in it's local database that resides in the install path (c:\program files\ind...\FactorySQL...). It runs these queries against the database on a reconnect. It does not maintain a transaction log or any extraneous data.
The transaction log is stored in a separate file from the data. However, there are many included management tools that are the better place to manage the transaction log (shrink, constrain size, delete, etc). I don't think that deleting the transaction log should affect the data - but it's hard to say what they did.
These are known "features" of a Microsoft SQL Server database that your IT department should be able to assist you with. Hopefully they have a DBA - or someone with some DB experience or training, not an 18 year old "helpdesker" maintaining your database. As an aside, you wouldn't have this problem with MySQL.
Nathan, thanks for the information. I am going to present it at the meeting on Monday. This way I can see where things stand. This brings up a question that is probably dumb and might already be answered somewhere but I thought I would ask anyway.
Lets say you have two Microsoft SQL servers. One as the primary and one as the back up. You have Factory SQL currently dumping its historical data to an instance within Microsoft SQL. Does Factory SQL, when updating, inserting, records into the primary Microsoft SQL server also update the information in the backup/secondary Microsoft SQL server or does the end user need to write a script or something so that in the event that the primary goes down and it switches over to the secondary all the same historical data is still there for Factory SQL and PMI to work with?
On a same kind of note, until the IT dept gets the backup/secondary Microsoft Server up, can the end user use the local instance of MySQL as the back up?
And if I understand this post correctly MySQL does not utilize any of these Log settings such as Microsoft SQL does? So I dont need to be concerned with Log settings within MySQL?
And finally the mention of the difference between Insert and Update. I know that I have my historical data set to Insert. The only data that is set to update is the Live data. But some of that I am moving out of the SQL tables and going with SQL tags. Would you say this would be a better approach?
Thanks and have a great day.
[quote="nathan"]You'll need to get the proper permissions or work with IT. You're looking for rapidly updating groups that are set to "bi-directional (either)" or "OPC->db". FactorySQL only writes to the database on a value change so precise floats that change by subtle amounts are your likely culprit. Setting a deadband can significantly decrease the number of writes. "Logging groups" (OPC->DB, set to INSERT a new record every time) tend to not be that bad. You know exactly how frequently they run queries.
SQL Server has a feature that stores a transaction log for "rollback" purposes. This involves storing every single query that was run on the database. That might be important if each query was a $$ transaction, but is just wasting resources in this case. Just tell IT that your application is running frequent UPDATE queries and that you don't need to keep a log - they'll understand that.
FactorySQL stores data in whatever tables the group is set to write to. It's simple. A group runs an UPDATE query every update rate, provided that a value changes and the trigger condition doesn't prevent it.
The FactorySQL caching mechanism stores the queries that it can't run in it's local database that resides in the install path (c:\program files\ind...\FactorySQL...). It runs these queries against the database on a reconnect. It does not maintain a transaction log or any extraneous data.
The transaction log is stored in a separate file from the data. However, there are many included management tools that are the better place to manage the transaction log (shrink, constrain size, delete, etc). I don't think that deleting the transaction log should affect the data - but it's hard to say what they did.
These are known "features" of a Microsoft SQL Server database that your IT department should be able to assist you with. Hopefully they have a DBA - or someone with some DB experience or training, not an 18 year old "helpdesker" maintaining your database. As an aside, you wouldn't have this problem with MySQL.