Factory Talk SE Historical Alarm database full - 10 GB limit

V0N_hydro

Member
Join Date
Sep 2010
Location
lower mainland
Posts
560
the factory talk SE (distributed) historical alarms activex widget was taking longer and longer to actually load any old alarms in to the table, and finally quit working at all.

There is a 10GB limit on the SQL Express database file size which we had run in to in only 8 months. It turns out that there were 16 alarms that were being disabled and enabled rapidly because they were programmed to be disabled when the analog input fault status was raised, and enabled when it was cleared. there was nothing wired to the analog inputs, they were provision for a 'future sensor', and should have had fault status the entire time.

I deleted 25 million rows from the database which took 4 hours and deleted the unused alarms from the programs so they won't cycle their enabled status anymore.

Does anybody have a method for automatically purging events older than X from the historical alarm database, or when the number of events is > Y?

My techconnect contract has expired, and the other references to the factory talk historical alarm database i've found on this forum don't have any resolutions other than Allen Bradley support telling their customer to break out the VBA and program it themselves.
 
The datalogger has a setup to delete older records from SQL Server automatically, but using the Alarm & Events server does not. I guess you wound have to write a procedure in SQL Server to do this.
 
Sql statement

I run into this every now and then where we'll have a bug in our code that turns an alarm on and off with every cycle. It will bring the alarm server and sometimes FTV to it's knees. We wrote SQL stored procedures that run every hour which check the number of rows in the alarms tables. When number of rows gets above a certain number we delete a big chunk of the rows, and get SQL to email us a notification. If we start getting a bunch of notifications, we know to go check out what's being logged and see if it's buggy code.
 
Detect and send email

USE Alarms -- name of your database alarms
DECLARE @tableRows int
SELECT @tableRows=COUNT(1) FROM [SQLServerName].[Alarms].[dbo].[AllEvent]

IF(@tableRows>200000) --checking if table has more than 200K records
BEGIN -- Send the email
DECLARE @NewBody varchar(max) = '[SQLServerName].[Alarms].[dbo].[AllEvent] table has more than 200K rows, Table Row Count is: ' + CONVERT(VARCHAR(30),@tableRows )

EXEC msdb.dbo.sp_send_dbmail
@profile_name = [SQL-Email],
@recipients = [[email protected]],
@copy_recipients = [[email protected]],
@body = @NewBody,
@subject = 'SQL ALERT!!!!: Regarding [SQLServerName].[Alarms].[dbo].[ALLEvent]' ;
END;
 
Delete Rows

Here we delete down to 100,000 rows but do it in blocks of 4000, so we don't lock up table.

We aren't SQL experts, so there might be a better way to do this, but it seems to work fairly well for us.


USE Alarms
DECLARE @TotalRowsToDelete int
DECLARE @RowsToDelete int
SELECT @TotalRowsToDelete = COUNT(*)-100000 FROM [SQLServerName].[Alarms].[dbo].[AllEvent]
WHILE @TotalRowsToDelete > 0 BEGIN -- start delete
SET @RowsToDelete = IIF(@TotalRowsToDelete>=4000,4000,@TotalRowsToDelete)
BEGIN TRANSACTION
DELETE TOP(@RowsToDelete) [SQLServerName].[Alarms].[dbo].[AllEvent]
COMMIT TRANSACTION
SET @TotalRowsToDelete -= @RowsToDelete
END;
 

Similar Topics

Good day I want to know if it is possible to set up the historical alarms on a Allen Bradley HMI without using a SQL server ? Is this possible...
Replies
1
Views
1,453
Looking to display data from the Factory Talk historian SE 4.0 on a screen in Factory Talk SE 8.1. Was wondering if anyone has run into this...
Replies
0
Views
1,574
Factory Talk SE 8.2 Vantage Point 7 Looking to display the historical data from a tag in an excel style format for the last 30 days. Was...
Replies
0
Views
1,193
Dear All, I want to know how to configure Historical Alarm using SQL server. Please Guide me ..
Replies
1
Views
2,862
Dear All I want know how to configure historical alarm using SQL in ftwiew Thanking you. Regards, souvik
Replies
1
Views
1,129
Back
Top Bottom