Clear transaction data timeout error

V5 Question
1

I have been trying to clear the transaction data from V5 but keep getting a timeout notification. I have read somewhere else that this may be related to the number of transactions - there are 1.2 million order transactions.

I found the script below at https://gist.github.com/emreeren/5806902 which I planned to run from Management Studio. It says it was last modified 2 months ago, is anybody able to confirm this is the latest version or have any experience of similar issues

DELETE FROM [TicketEntities]
GO

DELETE FROM [Tickets]
GO

DELETE FROM [AccountTransactionDocuments]
GO
 
DELETE FROM [AccountTransactions]
GO
 
DELETE FROM [AccountTransactionValues]
GO
 
DELETE FROM [Calculations]
GO
 
DELETE FROM [CostItems]
GO
 
DELETE FROM [InventoryTransactionDocuments]
GO
 
DELETE FROM [InventoryTransactions]
GO
 
DELETE FROM [Orders]
GO
 
DELETE FROM [PaidItems]
GO
 
DELETE FROM [PeriodicConsumptionItems]
GO
 
DELETE FROM [PeriodicConsumptions]
GO
 
DELETE FROM [ProductTimerValues]
GO
 
DELETE FROM [Payments]
GO

DELETE FROM [WarehouseConsumptions]
GO
 
DELETE FROM [WorkPeriods]
GO
 
UPDATE [Numerators] SET Number = 0
GO

UPDATE [EntityStateValues] SET EntityStates = '[{"S":"Available","SN":"Status"}]' Where EntityStates like '%Status%'
GO
2

may be you can use DELETE TOP 10000 from … query. You have giant amount of records.

3

Thanks for the suggestion and certainly worth a try, but to be honest, I am not really convinced this is the issue as 1.2m records in an MSSQL database is not excessive. My main concern at the moment is whether this is the correct script and latest version

4

Its unlikely to have changed in many versions, its only specifying tables not columns or specifics and there isnt going to be much change in these, not at least on sub version updates.
I had timeout before now clearing transaction after a couple of years. 200k tickets so not sure what that translatest to in orders and account transactions.
Remember there is allot more there than just the orders table.

My 200k tickets is likely to be over 1m orders, many thousands of account transactions, payments etc and thats without inventory which would at least need to be as many rows as orders.

Use in SQL manager, its more likely samba as a third party program has been timed out on its query, there is also likely a query timeout setting in sql like there is for php and most things.

5

The script works it is basic SQL you can test it on smaller # of tables if you wish. If it was not correct syntax it would have a different error than time out. Time out error means it can’t finish but likely was wprking

1 Like
6

I think when mine was timing out I just kept running it and it seemed to work through it before timing out each time.

1 Like
7

Thanks for the various responses. When I ran the 'Clear Transaction data" from SambaPOS it timed out after about 8.5 mins (which I would guess is actually a 500 sec timer). When I ran the script above from Management Studio, it completed in 5 min 40 sec. Unfortunately, before I started the exercise I had set Samba into training mode, however, that also timed out which locked the main database so that has taken a while to sort out. But it all seems to be working again now.

It does seem however that if the database is too large this causes some issues when running queries from Samba. As mentioned above the Orders table was about 1.2 million records this equates to 360k tickets and we don’t use the inventory. A backup is around 3GB. To me this not particularly excessive (the free version of MSSQL will support 10GB) and everything was running nicely, the problem has only become apparent as I am currently trying to configure SambaPOS V5 for use in a new restaurant utilising the original setup. That being the case, whilst my immediate problem is resolved, it may be worth flagging as an issue for further investigation.

Does anybody have any thoughts?

8

What sambapos does to run that script is really not much different. There are other things that affect database performance specifically hardware. When you enter training mode it creates a temp database instance and connects to it. It deletes it after reconnecting to main instance. This sounds like a hardware or system configuration issue.

9

Have you defragged or ran any maintenance tasks on the database? I can’t remember for sure but I thought Emre put a maintenance task in that might help. It’s in settings - program settings

1 Like
10

Also it is a good idea to start V5 from scratch and try to rebuild the setup vs upgrading from older v3 or v4.

1 Like
11

Use SSMS and run it in separate smaller pieces, in the same order as contained in the script. The most records will be found in Table [AccountTransactionValues], so try running that part by itself.

1 Like