Skip to main content What is Dynamics 365? Guided tours Customer stories Try our products Build your own agents CRM ERP Sales Service Sales Customer Insights Customer Service Contact Center Field Service Supply Chain Management Commerce Finance Project Operations Human Resources Business Central Pricing Business application topics Training & certifications Migrate to the cloud Documentation Events Dynamics 365 Blog Product updates Onboarding and implementation Community Find a partner Software Development Companies Partner resources Microsoft Marketplace Product documentation Technical support On-premises product support Contact us Try for free Sign in
  • 2 min read

AX for Retail: Manage data cleanup task in POS database

By ktruelse

Content types

Topics

Description:  

The cleanup procedures in POS are not fully implemented and therefore a small manual job can ensure that your database is not filling up with unnecessary records.

Resolution:

We create a job in SQL to run in a schedule. In this example we use 150 days. All Transactions that exceeds that date will be deleted.

The number of days depends on the frequency of the replication (P-JOB) and normally the value would be much lower like 1-5 days.

 

1. Go to your SQL Management studio

2. Go to Maintenance Plans

3. Create new plan and call it example DeletePOSData

 

4. Move over the Execute T-SQL Statement Task

5. Edit the TASK and add the delete statements

USE AXRETAILPOS
GO
— All POS transactions tables
— All records that are more than 150 days will be deleted

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

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

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

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

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

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

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

DELETE FROM dbo.RBOTRANSACTIONLOYALTYTRANS
WHERE REPLICATED = 1;

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

DELETE FROM dbo.RBOTRANSACTIONORDERINVOICETRANS
WHERE REPLICATED = 1;

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

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

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

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

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

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

DELETE FROM dbo.RBOTRANSACTIONVARIANTTRANS
WHERE 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.POSISLOG
WHERE LOGDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

 

6. Edit the Job schedule and let it run every day

7. Save the Maintenance job

8. Make sure that your SQL Agent runs, so the job executes every day

 

 

Author: Kim Truelsen

Blog date: 12-2-2012

Avatar of ktruelse

ktruelse

See ktruelse posts

Get started with Dynamics 365

Drive more efficiency, reduce costs, and create a hyperconnected business that links people, data, and processes across your organization—enabling every team to quickly adapt and innovate.

Learn More
Surface Pro Surface Laptop Surface Laptop Studio 2 Copilot for organizations Copilot for personal use AI in Windows Explore Microsoft products Windows 11 apps Account profile Download Center Microsoft Store support Returns Order tracking Certified Refurbished Microsoft Store Promise Flexible Payments Microsoft in education Devices for education Microsoft Teams for Education Microsoft 365 Education How to buy for your school Educator training and development Deals for students and parents AI for education
Microsoft AI Microsoft Security Dynamics 365 Microsoft 365 Microsoft Power Platform Microsoft Teams Microsoft 365 Copilot Small Business Azure Microsoft Developer Microsoft Learn Support for AI marketplace apps Microsoft Tech Community Microsoft Marketplace Software companies Visual Studio Careers About Microsoft Company news Privacy at Microsoft Investors Diversity and inclusion Accessibility Sustainability
English (United States)
Your Privacy Choices Opt-Out Icon Your Privacy Choices
Consumer Health Privacy Sitemap Contact Microsoft Privacy Manage cookies Terms of use Trademarks Safety & eco Recycling About our ads