DBA_sysjobhistory_driver.sql 996 Bytes
Newer Older
dwentzel's avatar
dwentzel committed
1 2
if exists (select * from sysobjects where id = object_id('DBA_sysjobhistory_driver') and sysstat & 0xf = 4)
DROP procedure dbo.DBA_sysjobhistory_driver
dwentzel's avatar
dwentzel committed
3 4 5
GO


dwentzel's avatar
dwentzel committed
6
CREATE PROCEDURE DBA_sysjobhistory_driver  
dwentzel's avatar
dwentzel committed
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
AS  
BEGIN  
	SET DEADLOCK_PRIORITY LOW
	DECLARE @job_id uniqueidentifier

  SET NOCOUNT ON  
  
--first, blindly set SQLAgent sysjobhistory to unlimited.  This shuts off sp_jobhistory_row_limiter
--which cuts down on waits and job blocking for larger customers.  
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=-1, 
	@jobhistory_max_rows_per_job=-1
	
  
  DECLARE CURSER CURSOR FOR
	SELECT job_id 
	FROM v_sysjobs
  OPEN CURSER
  FETCH NEXT FROM CURSER INTO @job_id
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
	--PRINT 'Running DBA_PM_sp_jobhistory_row_limiter for @job_id:  ' + convert(varchar(200),@job_id)
dwentzel's avatar
dwentzel committed
28
	EXEC DBA_sp_jobhistory_row_limiter @job_id = @job_id
dwentzel's avatar
dwentzel committed
29 30 31 32 33 34 35 36
	FETCH NEXT FROM CURSER INTO @job_id
  END
  CLOSE CURSER
  DEALLOCATE CURSER

  

END