I am after how to search to see what are the processes that are creating the most logs

[ThomasIvison]

I am sure I had a script to run on the DB that provided the processes that was creating logs which allowed me to switch off the logs so that the DB doesn't fill up. Has anyone got a script or is there a way I can check in BP without going through each process? 

Many Thanks

[SteveBoggs]

Hi Thomas,

You can review the following example SQL that should return a list of processes ordered by the amount of logging they perform: 

SELECT Results.name
	, Results.CountOfSessionLogs
 FROM (
	 SELECT P.name
		, COUNT(*) AS CountOfSessionLogs
	 FROM BPAProcess P WITH (NOLOCK)
	 INNER JOIN BPASession S WITH (NOLOCK) ON P.processid = S.processid
	 INNER JOIN BPASessionLog_NonUnicode LNU WITH (NOLOCK)ON S.sessionnumber = LNU.sessionnumber
	 GROUP BY P.name 
	 ) Results
ORDER BY Results.CountOfSessionLogs DESC​

Please note this SQL statement is provided as an EXAMPLE and should be reviewed and amended if necessary by your DBA before use.