AX零售：在POS DB管理数据清理任务

问题描述 :

POS的数据清理流程尚未完全实现，因此我们需要运行一个小的手工作业来确保您的数据库不会被无用记录填满。

解决方案：

我们创建了一个SQL定时脚本。在本文的例子中我们将时限设为150天。所有150天前的交易都会被删除。

脚本运行的频率由复制作业(P-JOB)的频率决定，通常低至1-5天。

1．打开SQL 管理工作室

2．打开维护计划

3．创建新计划并取名为DeletePOSData

4. 切换到执行T-SQL 语句任务

5. 编辑任务并添加删除语句

USE AXRETAILPOSGO-- All POS transactions tables-- All records that are more than 150 days will be deleted

DELETE FROM dbo.RBOTRANSACTIONBANKEDTENDE20338WHERE transdate < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONINCOMEEXPEN20158WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONINFOCODETRANSWHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONINVENTTRANSWHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONLOYALTYPOIN20296WHERE createdDate < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONLOYALTYPOINTTRANSWHERE createdDate < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONLOYALTYTRANSWHERE REPLICATED = 1;

DELETE FROM dbo.RBOTRANSACTIONMIXANDMATCHTRANSWHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONORDERINVOICETRANSWHERE REPLICATED = 1;

DELETE FROM dbo.RBOTRANSACTIONPAYMENTTRANSWHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONSAFETENDERTRANSWHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONSALESTRANSWHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONTABLEWHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONTENDERDECLA20165WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONVARIANTTRANSWHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

-- POS log table-- ALL data that is more than 150 days will be deleted

DELETE FROM dbo.POSISLOGWHERE LOGDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

6. 将作业时间表设为每天运行一次

7. 保存维护作业

8. 确保您的SQL代理正常运行，它会每天执行作业

原文地址：

Additional resources