SharePoint Timer Job History analysis SharePoint Timer Job History analysis
Arpit Patel

Arpit Patel

February 12, 2013

All Post
SharePoint Timer Job 76
Share:

Analyzing the SharePoint Timer Job History

At times one needs to analyze the timer job history to see what jobs have ran a long time. The Central Admin user interface limits you to a sequential view, although you can filter by Service or Web Application. The actual timer job history is maintained in a single table in the Config DB. Here’s a simple SQL Select to get the 100 longest running timer jobs in a time range:

 

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT top 100 [Id]
      --,[ServiceId]
      --,[WebApplicationId]
      --,[JobId]
      --,[ServerId]
      ,[Status]
      ,[StartTime]
      ,[EndTime]
      ,[WebApplicationName]
      ,[JobTitle]
      ,[ServerName]
      ,[DatabaseName]
      ,[ErrorMessage]
      ,datediff(S,StartTime,EndTime) as SecondsElapsed
  FROM [SharePoint_2013_Farm].[dbo].[TimerJobHistory]
  where StartTime > '2013-02-12 02:40:00' and EndTime < '2013-02-12 03:55:00'
  --note that the TimerHistory timestamp is always in GMT!
  order by SecondsElapsed desc
$events= Invoke-SQLcmd -Server "NY-SRV-SQLPRD02" -Database SharePoint_2013_Farm "select  JobTitle,WebApplicationName,ServerName,DatabaseName, StartTime,EndTime,ErrorMessage from dbo.TimerJobHistory where Status=3 and StartTime between GETDATE() -1 and GETDATE()"

Then in PowerShell:

foreach($event_num in $event) 
{ 
 
 Invoke-SQLcmd -Server "NY-SRV-SQLPRD04" -Database MYdbreports "insert into [SharePoint].[TimerJobHistory]  ( Status,StartTime,EndTime,JobTitle,ServerName,DatabaseName,ErrorMessage ) values ($event_num.Status,$event_num.StartTime,$event_num.EndTime,$event_num.JobTitle,$event_num.ServerName,$event_num.DatabaseName,$event_num.ErrorMessage)"
 
 }

Leave a Reply

Your email address will not be published. Required fields are marked *

Want to talk?

Drop us a line. We are here to answer your questions 24*7.