Factory Talk SE Historical Alarm database full - 10 GB limit

[V0N_hydro]

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.

[jkerekes]

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.

[Gabriel B. C.]

Alguem sabe como faco para restringir a quantidade de tags nos eventos do Alarm And Event Log Viewer?

[colemaj1]

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.

[V0N_hydro]

colemaj1, that sounds very clever. Could you share the SQL statements used to implement that functionality?

[colemaj1]

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 = [recipient1@someplace.com],
@copy_recipients = [recipient2@someplace.com],
@body = @NewBody,
@subject = 'SQL ALERT!!!!: Regarding [SQLServerName].[Alarms].[dbo].[ALLEvent]' ;
END;

[colemaj1]

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,@TotalRowsToDele te)
BEGIN TRANSACTION
DELETE TOP(@RowsToDelete) [SQLServerName].[Alarms].[dbo].[AllEvent]
COMMIT TRANSACTION
SET @TotalRowsToDelete -= @RowsToDelete
END;

[colemaj1]

We put the scripts in a SQL server agent job that runs every hour.