Источник:
http://blogs.msdn.com/b/axsupport/ar...-database.aspx
==============
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
Источник:
http://blogs.msdn.com/b/axsupport/ar...-database.aspx