Physical Deletion of Old Tickets in Database
Question asked by Yiorgos Palierakis - August 23 at 6:15 AM
Answered
Hi
 
We are running a SmarterTrack 12.6 system and the SQL Express database is approaching the size limit.  I note that when deleting a ticket from the system the actual ticket and all associated details are not removed from the actual database - it seems to just be marked as deleted so it does not appear in any search results.  This appears to be the case even with the automated deletion of closed tickets set for each department.
 
Is there a way to physically remove old data from the database itself (whether via SQL Management Studio or via the SmarterTrack Management Interface) safely?
 
Thanks

11 Replies

Reply to Thread
0
Eric McCarthy Replied
Employee Post Marked As Answer
Smarter Track will remove from the database any deleted tickets, and their associated items, that have been deleted for 90 days.
Eric McCarthy
Software Developer
SmarterTools Inc.
(877) 357-6278
www.smartertools.com
0
Yannick Guerdat Replied
Hello Eric, 
 
Is it anyway to run this script manually because I have a bunch of deleted tickets over 90 days who are never deleted. 
 
Thanks for your help

Artionet Web Agency | Director - Strategic Consultant Route de Moutier 109 | CH-2800 Delémont | Switzerland www.artionet.com Website, CMS, e-Commerce, e-Gov, Social News Room

0
Eric McCarthy Replied
Employee Post
No there is not a way to run this script manually. The purge thread runs daily and will purge all deleted tickets in batches of 200 approximately every second. This can take some time depending on how many tickets need to be purged. Several factors, including platform, system resets, or timeouts due to a bad connection could cause this thread to stop, making it seem as if it is not working.
Eric McCarthy
Software Developer
SmarterTools Inc.
(877) 357-6278
www.smartertools.com
0
Yannick Guerdat Replied
Hello Eric,
 
Thanks for your e-mail ! Some of my tickets have to be deleted since 2015 ! So I don't think that the script is working fine. 
 
If I try to stop the service it never stop, but I can restart it easily. 
 
So I guess it's a bug somewhere in my data-schema or somewhere else in the code. 

Artionet Web Agency | Director - Strategic Consultant Route de Moutier 109 | CH-2800 Delémont | Switzerland www.artionet.com Website, CMS, e-Commerce, e-Gov, Social News Room

0
Yannick Guerdat Replied
Is it possible to give me the SQL Scripts to run these tasks directly via SSMS ? 
 
Because it's not working and the error log is empty from error regarding the purge. 

Artionet Web Agency | Director - Strategic Consultant Route de Moutier 109 | CH-2800 Delémont | Switzerland www.artionet.com Website, CMS, e-Commerce, e-Gov, Social News Room

0
EL-Support Replied
Our Version 14.1 is configured to delete tickets older than 500 days.
 
We have the same issue that tickets are no longer visible within the ticket view/saerch but furthermore exist in the database (MySQL 5.7)
 
 
0
Yannick Guerdat Replied
Just as a tip, check that your App Pool user is Network Service or a user who has full access on the directory because he need to delete some old attachments too. 
 
I didn't have yet the confirmation from Smartertools support's team, but apparently the tickets are deleted during the week-end only. Most of my old tickets have been deleted last week-end but I didn't notice any change between yesterday and today. So I guess the clear are done only during the closing days. 
 
Hopefully it's can help you. 

Artionet Web Agency | Director - Strategic Consultant Route de Moutier 109 | CH-2800 Delémont | Switzerland www.artionet.com Website, CMS, e-Commerce, e-Gov, Social News Room

0
Andrew Barker Replied
Employee Post
Auto-deleted tickets are kept in the database for 90 days. 90 days after a ticket is deleted, it is purged from the database.This allows a window when a user with sufficient permissions can find the ticket, using advanced search, and undelete it.
 
Because all items, including attached files, are only purged 90 days after the ticket has been deleted, improperly set directory permissions can prevent the purge process from successfully completing. This also includes instances when the IIS application pool for the instance is set to use the process identity "ApplicationPoolIdentity". If you are seeing any problems with deleted tickets being purged, please make sure that your instance is running under either a specific account, or under the NetworkServices identity. Further, make sure that the account being used by the application pool has complete access to the SmarterTrack App_Data directory, and all of its subdirectories. If you continue to see problems after that, please contact our support department.

Andrew Barker
Software Developer
SmarterTools Inc.
(877) 357-6278
www.smartertools.com

0
Yannick Guerdat Replied
Hello Andrew,
 
Thanks for your feedback. It's me pretty clear that the tickets need to be older than 90 days to be delete. I had over 86'000 tickets undeleted in my database last week. After changing the user + rights nothing happens during 2 days and the third day (last saturday) the system has start to delete by batch of 300 my tickets in the database. 

I have now some 9'000 tickets still present and nothing has been deleted since monday. 

So I assume that the purge process is done only during the non working days. Does I'm wrong with this statement ? 

Best regards

Artionet Web Agency | Director - Strategic Consultant Route de Moutier 109 | CH-2800 Delémont | Switzerland www.artionet.com Website, CMS, e-Commerce, e-Gov, Social News Room

1
Eric McCarthy Replied
Employee Post
No, the purge process will run daily. I see that you have a support ticket open dealing with this issue. I would recommend bringing this up in your support ticket. This way you can get the personalized support you would need to discover what is causing the purge to not run on weekdays. 
Eric McCarthy
Software Developer
SmarterTools Inc.
(877) 357-6278
www.smartertools.com
0
Yannick Guerdat Replied
Thank you for your help guys

Artionet Web Agency | Director - Strategic Consultant Route de Moutier 109 | CH-2800 Delémont | Switzerland www.artionet.com Website, CMS, e-Commerce, e-Gov, Social News Room

Reply to Thread