SharePoint Timer Job History analysis

Arpit Patel

February 12, 2013

All Post

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)"
  
 }