Skip to main content
Dynamics 365
·
2 min read

AX Retail: Retail store maintenance and SQL Express

Description 

 Here are some tips to do maintenance on your store database running SQL express and how to shrink the size of the database.

 

Store transaction cleanup

It is important to cleanup the store database as many store databases in smaller shop only runs SQL Express editions. SQL Express has a 10 GB limit in size and when reached the system stops.

Since most customers are running pull job on very regular time schedule like every 15-30 minutes, the transaction data exists in the HQ database and makes the data in the store obsolete.

In the Dynamics AX Retail module we have an option to set the number of days these transactions should exists on the store database.

POS Functionality profile       
– Days transaction exists

Affected tables

RETAILTRANSACTIONTABLE

 RETAILTRANSACTIONTABLEEX5

RETAILTRANSACTIONBANKEDTENDERTRANS

RETAILTRANSACTIONDISCOUNTTRANS

RETAILTRANSACTIONINCOMEEXPENSETRANS

RETAILTRANSACTIONINFOCODETRANS

RETAILTRANSACTIONLOYALTYPOINTTRANS

RETAILTRANSACTIONORDERINVOICETRANS

RETAILTRANSACTIONPAYMENTTRANS

RETAILTRANSACTIONSAFETENDERTRANS

RETAILTRANSACTIONSALESTRANS

RETAILTRANSACTIONTENDERDECLARATIONTRANS

 

The data deletion will be executed for every closing of the shift on the POS.

 

Prerequisites:

The functionality is not implemented before KB2831335 and the required Dynamics AX POS build is

Dynamics AX 2012 build 6.0.1108.4260

Dynamics AX 2012 R2 build 6.2.1000.785

 

NB: Always implement the latest kernel build to get the latest hotfixes.

SQL Database maintenance

It is a good practice to do maintenance on the store database. Regular rebuilding and updating of indexes improves the performance.

In SQL Express the SQL server Agent is not available and therefore maintenance jobs can´t be scheduled.

 

Another way to Schedule a DB maintenance 

Windows Task Scheduler can be used to run a program like a command file (cmd). Make a new task and point the CMD file and add the
schedule.

SQL server has an interfase SQLCMD, which is available in SQL Express

 
  
  
  CMD file

Create a new text file and add the code inside and set the extension of the file to CMD

SQLCMD –d yourdatabase  -SLocalhost\SQLEXPRESS -HLOCALHOST –i c:\temp\sqlmain.sql

 

 SQL JOB (sqlmain.sql)

Create a new text file and add the code and set the extension of the file to sql

DECLARE @Database
  VARCHAR(255) = ‘YOURSTOREDB‘

DECLARE @Table
  VARCHAR(255)

DECLARE @cmd
  NVARCHAR(500)

DECLARE @Statement
  NVARCHAR(300)

 SET @cmd = ‘DECLARE TableCursor CURSOR FOR  SELECT ”[” + table_catalog + ”].[” + table_schema + ”].[” +  table_name + ”]” as tableName FROM ‘ +  @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’

  — create table cursor

 EXEC (@cmd)

 OPEN TableCursor

 FETCH NEXT FROM TableCursor INTO @Table

 

 WHILE @@FETCH_STATUS = 0

 

 BEGIN

 SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table +  ‘ REBUILD;’

 EXEC sp_executesql @cmd

 SET @Statement = ‘UPDATE STATISTICS ‘ +  @Table + ‘ WITH FULLSCAN’

 EXEC sp_executesql @Statement

 FETCH NEXT FROM TableCursor INTO @Table

 END

 CLOSE TableCursor

 DEALLOCATE TableCursor

 

 

For SQL Express you would need to allow remote connections, enable the TCP/IP, Named pipes protocols in the sql configuration tool and start the sql browser.

 

 

 

Author: Kim Truelsen, MS Escalation Engineer

Date: 10/1- 2014

 

ktruelse

Related posts