You are not registered yet. Please click here to register!


 
 
plc storereviewsdownloads
This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc.
 
Try our online PLC Simulator- FREE.  Click here now to try it.

New Here? Please read this important info!!!


Go Back   PLCS.net - Interactive Q & A > PLCS.net - Interactive Q & A > LIVE PLC Questions And Answers

Reply
 
Thread Tools Display Modes
Old July 23rd, 2015, 11:48 AM   #1
V0N_hydro
Member
Canada

V0N_hydro is offline
 
Join Date: Sep 2010
Location: lower mainland
Posts: 562
Factory Talk SE Historical Alarm database full - 10 GB limit

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.
  Reply With Quote
Old July 23rd, 2015, 12:58 PM   #2
jkerekes
Member
United States

jkerekes is offline
 
jkerekes's Avatar
 
Join Date: Aug 2007
Location: NJ
Posts: 2,348
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.
__________________
John
  Reply With Quote
Old April 6th, 2018, 10:05 AM   #3
Gabriel B. C.
Member
Brazil

Gabriel B. C. is offline
 
Join Date: Oct 2017
Location: Porto Alegre
Posts: 57
Alguem sabe como faco para restringir a quantidade de tags nos eventos do Alarm And Event Log Viewer?
  Reply With Quote
Old December 26th, 2018, 02:04 AM   #4
colemaj1
Lifetime Supporting Member
United States

colemaj1 is offline
 
Join Date: Dec 2011
Location: Greenwood, MS
Posts: 19
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.
  Reply With Quote
Old December 29th, 2018, 11:49 AM   #5
V0N_hydro
Member
Canada

V0N_hydro is offline
 
Join Date: Sep 2010
Location: lower mainland
Posts: 562
colemaj1, that sounds very clever. Could you share the SQL statements used to implement that functionality?
  Reply With Quote
Old December 29th, 2018, 12:16 PM   #6
colemaj1
Lifetime Supporting Member
United States

colemaj1 is offline
 
Join Date: Dec 2011
Location: Greenwood, MS
Posts: 19
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 = [recipient1@someplace.com],
@copy_recipients = [recipient2@someplace.com],
@body = @NewBody,
@subject = 'SQL ALERT!!!!: Regarding [SQLServerName].[Alarms].[dbo].[ALLEvent]' ;
END;
  Reply With Quote
Old December 29th, 2018, 12:21 PM   #7
colemaj1
Lifetime Supporting Member
United States

colemaj1 is offline
 
Join Date: Dec 2011
Location: Greenwood, MS
Posts: 19
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,@TotalRowsToDele te)
BEGIN TRANSACTION
DELETE TOP(@RowsToDelete) [SQLServerName].[Alarms].[dbo].[AllEvent]
COMMIT TRANSACTION
SET @TotalRowsToDelete -= @RowsToDelete
END;
  Reply With Quote
Old December 29th, 2018, 01:09 PM   #8
colemaj1
Lifetime Supporting Member
United States

colemaj1 is offline
 
Join Date: Dec 2011
Location: Greenwood, MS
Posts: 19
We put the scripts in a SQL server agent job that runs every hour.
Attached Images
File Type: png job.PNG (24.0 KB, 61 views)
  Reply With Quote
Reply
Jump to Live PLC Question and Answer Forum

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Topics
Thread Thread Starter Forum Replies Last Post
How to Backup and Restore Factory Talk view SE Project for Editing Tim Ganz LIVE PLC Questions And Answers 6 June 6th, 2018 04:15 PM
Factory Talk - tag database error Narlin LIVE PLC Questions And Answers 7 October 27th, 2017 09:49 AM
How to create Popup alarm window in Factory Talk SE local minaykha LIVE PLC Questions And Answers 0 September 24th, 2013 08:49 PM
FT View SE 6.10 Alarm and event log database error Pexy LIVE PLC Questions And Answers 1 September 13th, 2012 04:51 PM
Factory talk View Change alarm description Olnine enault LIVE PLC Questions And Answers 2 June 18th, 2008 08:46 AM


All times are GMT -4. The time now is 05:34 PM.


.