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)" } |
Want to talk?
Drop us a line. We are here to answer your questions 24*7.