SQL Queries : iVend Enterprise /HO database Purge

SQL Queries : iVend Enterprise /HO database Purge

****************INTERNAL TO SUPPORT TEAM ( Not for Customers)  *************************
 
Note:-This article is created only for Support team and will be done along  with  product expert.

Environment: 

iVend 6.6 integrated with SAPB1

Problem Statement: 

iVend 6.6 Enterprise/HO database is getting increased in size becuase of high volume of tranaction data and there is need to purge the iVend Enterprise /HO database

Symptoms: NA

Resolution/Work Around: 

Step1:

Schedule in SQL Job: 
Declare @PurgeDate Datetime
Declare @FinalPurgeDate Nvarchar(50)
SET @PurgeDate = '2023-01-01 00:00:00.000'   -- <<YYYY-MM-DD>> This is Cutoff Date you want to Choose for Purge Database i.e after Purging data will be available after this date only.
-- This Job will Delete 5 Days Data in One Execution and will execute daily untill Purge Date will Match. In case you want to Increase 5 days to 7 days you can change 5 to 7 in Below Query.

 Select  @FinalPurgeDate=  CONVERT(nvarchar(50), CASE WHEN ((min(businessdate)+5) < @PurgeDate) THEN (min(businessdate)+5 ) ELSE @PurgeDate  END , 112) from trxtransaction (nolock)

Print @FinalPurgeDate

exec sp_DatabasePurge @FinalPurgeDate , 0,1

Step2 :

Apply the attached SQL Procedure sp_DatabasePurge 

Step3 :

CUstomer need to validate on daily bases via below query that data was deleted or not.

Select min(businessdate) from trxtransaction (nolock) 

Recommendations /Suggestion:  NA

    • Related Articles

    • Cannot open database 'CXSIntegration' login failed

      Environment: iVend 6.6 Problem Statement: Cannot open database 'CXSIntegration' login failed Symptoms: Environment Specific Resolution/Work Around: It seems wrong SQL password is entered for user "sa". So need to check correct password for SQL login ...

    • Open Stock Transfers- SQL Queries

      Environment: iVend 6.6 with SAPB1 10. Problem Statement : There is need to have SQL Queried to find Open Stock Transfers in iVend Enterprise database SQL Queries : Below Queries are used : a) select ...

    • SQL Queries to find the database size

      Environment: Not Environment Specific Problem Statement: SQL Queries to find the database size Solution: There are below SQL Queries , used to analyze the iVend database : SQL Query for database size SELECT DB_NAME(database_id) AS DBName,Name AS ...

    • iVend POS - Purge Data to Reduce iVend Store database size

      Introduction The Data Purging feature in iVend helps the Administrator to control the size of the iVend database at each store. Data purging at stores is controlled centrally and is managed from the iVend Enterprise at Head Office. The Administrator ...

    • iVend Offline POS - Purge Data for Offline POS Database

      Product Query Is there any process to purge Offline POS Database? From HO Server only is possible to purge the store database but not offline pos. Environment iVend 6.6 Solution As per current iVend functionality Data purging (from iVend Management ...