How do I find job duration in minutes?

How do I find job duration in minutes?

The below script shows the duration in minutes based on dbo.Log for a given job (replace the parameter below by Job.Name).

EXEC PrsDropObject #JobLog

SELECT *

INTO #JobLog

FROM Log

WHERE Category = ‘RunJob’

AND Timestamp > DATEADD(month, -6, GETDATE())

AND (   Message LIKE ‘Begin proc%’

OR Message LIKE ‘End%proc%’

OR Message LIKE ‘Param%fd3b1628-8735-4e8a-9719-4cea4e34d924’) –change this parameter

SELECT Job.DisplayName

, MAX(lBegin.LogID)        AS BeginLogID

, MAX(lBegin.Timestamp)    AS BeginTimestamp

, MIN(lEnd.LogID)            AS EndLogID

, MIN(lEnd.Timestamp)        AS EndTimeStamp

, DATEDIFF(minute, MAX(lBegin.Timestamp), MIN(lEnd.Timestamp)) AS Duration_Minutes

FROM #JobLog AS lParam

JOIN #JobLog AS lEnd    ON lEnd.LogID    > lParam.LogID

JOIN #JobLog AS lBegin    ON lBegin.LogID < lParam.LogID

LEFT JOIN Job ON RIGHT(lParam.Message, 36) = Job.Name

WHERE lParam.Message LIKE ‘Parameter%’

AND lEnd.Message     LIKE ‘End%proc%’

AND lBegin.Message LIKE ‘Begin%proc%’

GROUP BY lParam.logid, lParam.Timestamp, Job.DisplayName

ORDER BY DisplayName

You should also make sure that the Service Account then has access to the ArkievaServer database. The Location of that database is defined in the config file of the services.

By default, this database will reside on the following SQLserver Instance: (localdb).\Arkieva; the name of the database is ArkievaServer. The service Account needs to exist as a sysadmin on that server and be a member of the db_owner role at the database level. If you have no SQL Management Studio on this server, you can type the following lines in a command prompt.

In the below example the Service Account name is [ONTEX-NET\svc-Arkieva]. Every time you press enter after the go command the lines above will be executed.

    • Related Articles

    • Run LP Remotely job not working?

      You need elevated privileges to execute the Create Job and Run Jobs. If these elevated privileges are not set, you will not be able to run jobs remotely in the LP. 

    • Best Method is not showing when run as a job?

      The reason Best Method is not showing when run by a Job is because of the service account. Give the service account full access to all data in security, then try running the job again. 

    • Job are not being picked up from the pending jobs table?

      Check if the service account in security has all access rights in the application.  Check how long each step of the job is taking by creating a Query. Select * From Log WHERE [Formatted Message], for example: '% Insert Job ID Here %'. Check if the ...

    • Application cannot find column mapping?

      If Arkieva is not fable to map the columns to the table,  the table is probably in another schema than the dbo. Even if you are able to map the column to other tables, if the table is in another schema, the column mapping will not function because ...

    • How do I manually set the impersonation account to run Arkieva services?

      When installing the Arkieva application as another user than the service account, the Service account will not have access to the Message Queues, which must be added afterwards. Please watch this video to learn how. Also, change the Log On As account ...