SharePoint Timer Job History analysis

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

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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