SAP xMII Log/ SQL transaction Log

[Former Member]

We have an application where we are inserting data from a PLC to the SQL Server using the xMII.

The SQL server no. of times gives the Transaction Log Full error.

This causes the error any data insertion or deletion in SQL

In tern the xMII logs error, the log file for a day (in the Lighthammer\Logs\cms.log)is seen as large as 15 GB.

We are not able to open such a huge file...

Moreover we are unable to know if there are anyu other errors other than due to the SQL logged by xMII

How can we see the Log created by xMII, this will help us in troubleshooting .

Message was edited by:

Amol Kurdukar

[Former Member]

Many thaks to Ryan for pointing the correct way for the solution.

[jcgood25]

Amol,

Why in the world are you using xMII as a Historian? Taking live PLC values and stuffing them into a SQL Server table every minute is rediculous. If you are having issues with the size of the transaction log in the database I can only imagine that the database size itself will be equally rediculous.

If you are only logging a handful of tags from the PLC into a database a few times per day then I suppose that is acceptible, but anything beyond that is a misuse of the product.

Regards,

Jeremy Good

[Former Member]

Hi All,

Thanks for the help.

Ryan,

I have resolved the issue of transaction log by archiving the Log at increased frequency.

Jeremy,

We are required to have the PLC data in SQL as the Customer wants to have that and they do not have any other database, SCADA etc.

The SQL server logs (consolidates) not only the PLC data but data from some other devices as well

Moreover this is data then used for confirming the production to SAP and maintaining the status of the confirmation. This also involves queuing of the data in SQL if the data is not confirmed to SAP for any reason.

We are not logging the data every minute, The data is logged depending on a batch completion, shift completion etc.

Hope this clarifies, let us know your comments.

[Former Member]

You mention the c:\Lighthammer\Logs\cms.log file is 15 GB. This file is used by xMII, not really SQL Server.

To change the settings for this file, go to the Log Configuration under Log Management on the Admin Menu of xMII (http://localhost/Lighthammer/Admin/LogConfigList.jsp). In here you can set the log configurations of all of the log files. Set the General log's Log Level to "Error" and set the Retention Time to 30.

You may have to restart the ServletExec-xMII service for the change to take effect. I would do it anyway to start a new log file.

[Former Member]

Thanks

Yes, I will do the setting in the xMII and then monitor the size of the Log.

But my original problem is with SQL server transaction Log which is intern affecting the xMII log.

Moreover I am not knowing when the Transaction log is getting full, I am loosing the data as the Transactions from xMII are unable to insert the data in the SQL once the transaction log is full.

[Former Member]

Thats the reason I asked you to check the transaction log size specified in your SQL server. You specify this at the time of creating a database on the server.

I guess you can find it by righ-clicking the DB and properties.

May be you need to increase this size or make it expandable or something.

[Former Member]

Why don't you set the Transaction Log to Log Level "Error". It sounds like you have it on Debug.

[Former Member]

Hi

Thanks,

I am using SQl server 2005.

Can you guide me where do I select this option of "Error" log.

All we have done is created a JOB which archieves the Log after everyday (the file for a day is around 500 MB !!, 1 day might is not sufficient it must be less in our case .. or we need to set something which would create very less Log.

[Former Member]

What is the size specified for the Transaction Log in your SQL server?

[Former Member]

Hi

I am using the SQL server 2005 , i have not set the Transaction log size anywhere but the Transaction log shipping frequecy is set for min. setting available i.e one day.

I can observe that the size seen is 500 MB everyday.

[Former Member]

In Microsoft SQL Server Management Studio, browse to your database. Right click and go to Properties.

Then select "Files" from the menu on the left.

You'll see two Logical named files (<database>_dat and <database>_log). For the log file, scroll right and you'll see the "Autogrowth" column. By default this is set to true as well as the option for "Unrestricted File Growth". If you are going to have Autogrowth enabled, then set the Maximum file Size to something reasonable (ie, 100MB).

I'm still confused on how your log file is getting so big in the first place. Also if you are constrained by disk space, consider moving the log file to a larger drive partition.