# How to create a script to clear Database every day?

**URL:** <https://forum.inductiveautomation.com/t/how-to-create-a-script-to-clear-database-every-day/72575>\
**Category:** Ignition\
**Created:** [April 18, 2023, 1:38pm UTC](https://forum.inductiveautomation.com/t/how-to-create-a-script-to-clear-database-every-day/72575 "2023-04-18T13:38:47Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tzbh1234](https://avatars.discourse-cdn.com/v4/letter/t/c67d28/32.png) [@tzbh1234](https://forum.inductiveautomation.com/u/tzbh1234)\
**Post date:** [April 18, 2023, 1:38pm UTC](https://forum.inductiveautomation.com/t/how-to-create-a-script-to-clear-database-every-day/72575/1 "2023-04-18T13:38:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [April 18, 2023, 2:42pm UTC](https://forum.inductiveautomation.com/t/how-to-create-a-script-to-clear-database-every-day/72575/2 "2023-04-18T14:42:15Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [April 18, 2023, 2:42pm UTC](https://forum.inductiveautomation.com/t/how-to-create-a-script-to-clear-database-every-day/72575/3 "2023-04-18T14:42:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Kfoldesi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kfoldesi/32/11327_2.png) [@Kfoldesi](https://forum.inductiveautomation.com/u/Kfoldesi)\
**Post date:** [April 18, 2023, 7:06pm UTC](https://forum.inductiveautomation.com/t/how-to-create-a-script-to-clear-database-every-day/72575/4 "2023-04-18T19:06:33Z")

</div>

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

```auto
-- =============================================
-- 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

```