SQL Query to short process logs by size

[Ismael]

Hi everyone,

I am wondering if you know what would be the query that I have to execute in order to find what are the biggest logs in my database per process.
I am talking about the logs in table BPASessionLog_NonUnicode. So if I find the largest processes I will be able to archive them keeping the smallest.

Anyways I will try to get the query myself and if I get it I will post it here. I was just wondering if you already had a quick solution.

Thanks a lot!!

 

[Emyr_Morris30]

Hi Ismael,

I have a similar problem with the Table you've noted above (the SessionsLog_NonUnicode table).

Mind seems to just grow and grow!

Did you manage to find a query for it? I am currently looking now myself !

Kind Regards,

Emyr

 

[Ismael]

Hi Emyr,

What we use is the following query. Is not the real amount of MB since what it does is summing the length of each separate process with name of the process, length and date.

SELECT IIF( [processname]='', [objectname], [processname]) as 'name'
,FORMAT([startdatetime],'d', 'en-gb') AS 'groupDate'
,SUM(CAST(Datalength([attributexml]) as bigint)) + SUM(CAST(Datalength([result]) as bigint)) as 'LenDtat'
FROM [YOURDATABASENAME].[dbo].[BPASessionLog_NonUnicode]
GROUP BY IIF( [processname]='', [objectname], [processname]) ,FORMAT([startdatetime],'d', 'en-gb')

Check it out if it works for you.

In case you want to clean that table you are talking about, I also did another post with the SQL script that it will clear that table:

http://www.rpaforum.net/threads/bpasessionlog_nonunicode-archivation.2095/#post-7060

Cheers,
Ismael.