cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query to fetch results for process logging extensively

Saif
Level 3
Can someone please help with a SQL query to check which process is logging excessively in database?
10 REPLIES 10

Hi Saif,

This is rather strange to happen. But if it is happening you can refer to the master BPAProcess table that has the list of all the processes in your environment but you need to create a join from the current query till that table itself which can happen only via a secondary table called as BPASession table. The query is as follows:

SELECT BPAProcess.name,CONVERT(DECIMAL(38,12),Sum(
CONVERT(DECIMAL(38,12), IsNull(DataLength(BPASessionLog_NonUnicode.sessionnumber), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stageid), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stagename), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stagetype), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(processname), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(pagename), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(objectname), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(actionname), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(result), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(resulttype), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(BPASessionLog_NonUnicode.startdatetime), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(BPASessionLog_NonUnicode.enddatetime), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(attributexml), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(automateworkingset), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(targetappname), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(targetappworkingset), 1))
)/1073741824) AS session_log_size
From
BPASessionLog_NonUnicode INNER JOIN BPASession 
ON BPASessionLog_NonUnicode.sessionnumber = BPASession.sessionnumber INNER JOIN BPAProcess 
ON BPASession.processid = BPAProcess.processid
GROUP BY
BPAProcess.name
ORDER BY session_log_size DESC​


19035.png

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.