How to create a script to clear Database every day?

Ignition
1

Hi,
I have ignition 7.9.11, I want to create a script that checking every day which data exist in database at least one year or more and delete it.
Which subject I need to read to work about it ?
Thanks.

2

IIRC, Your using SQLServer. I would personally do it in small batches. and run every 10 minutes (YMMV)

query = 'DELETE TOP(1000) FROM mytable WHERE t_stamp < CAST(GETDATE()-365 as DATE)'
system.db.runUpdateQuery(query, 'dbConnectionName')
1 Like
3

If it is the historian database then use the auto-prune feature in gateway | Config | Tags | History and select the relevant history provider.

4

I like to keep SQL stuff in SQL. This is a trigger on a table that logs every time the operator creates a batch:

-- =============================================
-- Author:		Keith Foldesi
-- Create date: 2/17/2023
-- Description:	Manage size of data
-- =============================================
ALTER TRIGGER [dbo].[PruneEirich] 
   ON  [dbo].[TblDataEirich] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

declare @DelB4 as datetime = dateadd(day,-365,getdate());

	with x as (SELECT TOP 100  id
	FROM [TblDataEirich]
	where TStamp <= @DelB4
	order by TStamp )

	delete x 

END