Developer forum

Forum » CMS - Standard features » Purging solution reports ... what shoud it do?

Purging solution reports ... what shoud it do?

E-mail notifications
Peter Leleulya
Reply
Posted on 21/10/2015 14:00:15

The Dynamicweb backend of one of our clients shows a message in the top bar.
The mesage states that the session statistics are over 4GB in size and could cause performance issues and someone should clean it up.

I searched this forum what to do and saw many discussions on this topic and many SQL queries I should run.
I'm not a big fan of running delete queries on tables with data which i'm not sure their purpose is ...
So I found a mention on purging solution reports through the Dynamicweb backend.
Management center --> System --> Solution reports

I took the biggest one, selected the date of 1 mont ago and pushed the corresponding "Purge" button.
A message appeared "One moment please, the database is schrinking" ...
It stayed in this state untill the end of my working day yesterday.

I opened the same page today to find the size not to have changed and did exactly the same steps.
That was around 08:30h this morning and the screen is in the same stat now at 14:00h ...

First of all, am I pushing the right buttons to decrease the session statistics?
Second, is this action going to end sometime in the near future, or did it crash an keeps aaking me to wait?
Third, what should happen in technical terms? What should it be deleting?

The database is configured to recovery mode simple.

I hope anyone can help ....

Excuse the dutch sample ...

SolutionReports.PNG

Replies

 
Nicolai Høeg Pedersen
Posted on 21/10/2015 16:27:49
Reply

It probably is broken. So delete less at the time or do it directly on the database.

After that has been done, it might be needed to do a shrink on the database on the server as DW might not have sufficient rights to do it. Also check the database auto grow settings. Sometimes they grow in enormoues rates, which is not good.

BR Nicolai

 
Peter Leleulya
Posted on 28/10/2015 15:44:48
Reply

Hi NIcolai,

A late response, because I wanted to discuss this issue with someone who was on vacation at the time.
I tried schrinking some of the tables directly on the database, but that had no signifficent changes as result.
Deleting data by sql statements seem to be the only solution :(
But I dont know how these tables interact and dont know if there are any cascading relations or not.
Can you provide me with a delete script which is safe to execute?

Some strange thing are going on, some of the indes are many times bigger than the data itself ..
See attachment

Autogrowth settings are ok by the way ...
 

db-sizes.PNG
 
Nicolai Høeg Pedersen
Posted on 29/10/2015 08:55:27
Reply

Hi Peter

Just delete the rows by date. There are no relations, so that is safe. I can see the tables are huge, you might want to take a look at this:

http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

Before deleting, you could remove all the indexes - it could be that you have too many and hence the size.

Attached the data definition on how the tables and the indexes should look like. So you can remove your indexes, purge the tables, and add the indexes from the attachment.

BR Nicolai

 
Peter Leleulya
Posted on 29/10/2015 08:57:55
Reply

Thank you Nicolai.

 

You must be logged in to post in the forum