Hi, the database auto backups takes like 15-20 minutes to get ready, it is like 570mb , so I decided to remove all transactions and customers because they were like 5 years old, but I leave intact the users and all configuration. The problem is that the backups are the same size and takes the same time to get ready, any ideas on how to get a smaller backup? what is causing the enourmous size? Thanks in advance for your help.
570mb is not really that big. it shouldnt take 15-20 min for 520 mb. What kind of system is it running on?
Do you use standard hard disk or an SSD on your device?
My server:
Intel(R) Core™ i3-4150 CPU @ 3.50GHz 3.50 GHz
8.00gb RAM
Windows 10
SSD 120gb kingston
I know is not a very powerful pc, do you think this is the cause of the problem? I thought that 570mb was too heavy for a database with zero transactions.
Did you use or are you still using previous version of metrik?
1 Like
That pc spec is plenty. Even at that size it shouldn’t take that long. Maybe 2-3 min but certainly not more than 10
Are you sure there is enough space on the hard drive. 120 gig is very small for windows 10 and storing backups.
Too add to this, have a look @ disk usage by table in SSMS. Right click on Database -> Reports -> Standard Reports -> Disk Usage by Table then sort by size.
Definitely the backup should not take long. As an example, on a lower spec system - Intel J1900 CPU, 4Gb RAM, 128Gb SSD - I have a customer who does over a thousand transactions a day and keeps all data from day one, so around 4 years worth at this stage. Their database is 2.1Gb in size and 291Mb when zipped by SambaPOS, it takes maybe 2 minutes or less to backup.
As I find some staff will get inpatient when waiting for the backup to complete, I just turned off the “auto backup on end work period” in SambaPOS and handle the backups silently in the background outside of SambaPOS using the free SQLBackupFree tool (it’s pretty old but works fine even in Win 11). This way it is never tampered with and you can also configure it to send an email if there is any failure. I just schedule it to backup once daily around the time near end of day. I set this up for any larger customers what have a lot of data in SambaPOS but I think its good practice for any setup.
2 Likes
Good evening, something similar happened to me in a clientwe after deleting all the transactions I noticed that it took a long time to backup when it closed and it was strangely very large in size.
I was helped by a great person and in the database of the program what caused that amount of information I think I remember it was the tables related to error reports or something like that, I think I remember.
I can’t help you how to do it because they helped me, but I think the solution goes that way.
If you don’t know how to search in the database that occupies space, look for someone like I did.
I hope it helps you friend.
Ahh and thanks markjw for the sotware you mentioned to make copies, I had no idea it existed and I think it will be very helpful (this is the good thing about the forum), we can share information to improve the work day by day.
Thanks
1 Like
Thank you very much for you suggestions, I will try it and let you know what works for me
I had a customer with similar problems some years ago with some of the processes running slowly and getting slower by the day. I eventually traced this to fragmentation of the indexes in the database. If you look at this site https://ola.hallengren.com/ there is an excellent explanation of this, as well as code you can use to optimize the database and rebuild the indexes.
I now have these processes as stored routines in the database and they are run daily triggered by the last shift of the day closing the work period and have had no repitition of the problem. I also run the database backup using the code from this site and it takes less than two minutes to back up a 2.6GB database with more than 4 years of transactions.
Unrelated to your issue but of some interest to compare with markjw’s figures, I use 7zip to compress the database prior to uploading it to Onedrive as a backup. This compresses a 2.6GB database to 199MB.
1 Like
Yes, you are right, the table ReportTemp and Tasks are the tables occuping a lot of space, so what I did is to truncate the tables, since the Delete command doesn’s get rid of the space in disk:
TRUNCATE TABLE REPORTTEMP
TRUNCATE TABLE TASKS
After that I rebuilded indexes in Samba: Settings/Maintenance/rebuild indexes
Doing that the samba backup reduced from 0.5gb to only 11mb and it takes only 5 seconds to complete.
Thank you very much for your help! and thank you yo everyone that helped!
1 Like
Keep in mind that if the triggers aren’t removed that populate the ReportTemp table, the condition will return.
Running this in SSMS or as a database task against the database will remove those triggers.
[CBL]metrik_removal.zip (2.7 KB)
4 Likes
Excellent, Thank you very much @Memo
1 Like