If you're like me, it's a lot easier to understand things with a picture. With that in mind, here's a script you can run as a Job which will print out a visual representation of the Jobs that ran in the last 24 hours. The results are emailed to whatever address you provide (near the end of the script).
------------------------------------------------------------------------------- -- Prints a primitive bar chart that shows all the jobs that ran during the -- last 24 hours. Useful for visualizing overlap. -- -- 2010-08-17 - Larry Leonard ------------------------------------------------------------------------------- USE YourDatabase SET STATISTICS IO OFF SET NOCOUNT ON GO -- This is the only variable you can set. It is the width you want the -- output to be, in characters. DECLARE @nScreenDotWidth INT SET @nScreenDotWidth = 80 ------------------------------------------------------------------------------- -- Create the temp table that will hold all the Job History data. ------------------------------------------------------------------------------- ;WITH cte AS ( SELECT jv.name AS 'sJobName', jv.description AS 'sJobDescription', SUBSTRING(CAST( jh.run_date AS CHAR(8)), 5, 2) + '/' + CAST(RIGHT(jh.run_date, 2) AS CHAR(2)) + '/' + CAST(LEFT (jh.run_date, 4) AS CHAR(4)) AS 'sRunStartDate', CAST(LEFT(RIGHT(STUFF(jh.run_time, 1, 0, '0'), 6), 2) AS CHAR(2)) + ':' + CAST(LEFT(RIGHT( jh.run_time, 4), 2) AS CHAR(2)) + ':' + CAST(RIGHT( jh.run_time, 2) AS CHAR(2)) AS 'sRunStartTime', ((60 * 60 * CAST( LEFT(STUFF(jh.Run_Duration, 1, 0, REPLICATE('0', 6 - LEN(jh.Run_Duration))), 2) AS INT)) + ( 60 * CAST(SUBSTRING(STUFF(jh.Run_Duration, 1, 0, REPLICATE('0', 6 - LEN(jh.Run_Duration))), 3, 2) AS INT)) + ( CAST( RIGHT(STUFF(jh.Run_Duration, 1, 0, REPLICATE('0', 6 - LEN(jh.Run_Duration))), 2) AS INT)) ) / 60 AS 'nRunDurationMins' FROM msdb.dbo.sysjobs_view jv JOIN msdb.dbo.sysjobhistory jh ON jv.job_id = jh.job_id WHERE jh.step_id = 0 -- "Job is completed" step. ) SELECT sJobName AS 'sJobName' , sRunStartDate + ' ' + sRunStartTime AS 'sRunStart' , nRunDurationMins AS 'nRunDurationMins' INTO #Chart FROM cte ------------------------------------------------------------------------------- -- Add the runs' start and stop time from the ridiculous format returned by -- sysjobhistory. Use this info to only look at last 24 hours. ------------------------------------------------------------------------------- ALTER TABLE #Chart ADD dtRunStart DATETIME NULL ALTER TABLE #Chart ADD dtRunEnd DATETIME NULL UPDATE #Chart SET dtRunStart = CAST(sRunStart AS DATETIME) UPDATE #Chart SET dtRunEnd = DATEADD(MINUTE, nRunDurationMins, dtRunStart) DELETE FROM #Chart WHERE dtRunEnd < DATEADD(HOUR, -24, GETDATE()) OR nRunDurationMins = 0 ------------------------------------------------------------------------------- -- Calculate each job run's start and stop points on the chart. ------------------------------------------------------------------------------- DECLARE @dtLatest DATETIME SELECT @dtLatest = MAX(dtRunEnd) FROM #Chart DECLARE @dtEarliest DATETIME SELECT @dtEarliest = DATEADD(hour, -24, @dtLatest) DECLARE @nMinsPerDot INT SET @nMinsPerDot = DATEDIFF(MINUTE, @dtEarliest, @dtLatest) / @nScreenDotWidth ALTER TABLE #Chart ADD StartDot INT NULL ALTER TABLE #Chart ADD EndDot INT NULL UPDATE #Chart SET StartDot = DATEDIFF(MINUTE, @dtEarliest, dtRunStart) / @nMinsPerDot UPDATE #Chart SET EndDot = DATEDIFF(MINUTE, @dtEarliest, dtRunEnd) / @nMinsPerDot ------------------------------------------------------------------------------- -- Iterate through the job runs, creating a line for each one. ------------------------------------------------------------------------------- DECLARE @nMaxJobNameLen INT SELECT @nMaxJobNameLen = MAX(LEN(sJobName)) FROM #Chart DECLARE @sJobName NVARCHAR(4000) DECLARE @StartDot INT DECLARE @EndDot INT DECLARE @sChart NVARCHAR(MAX) SET @sChart = '' DECLARE @sLine NVARCHAR(4000) DECLARE cur CURSOR FOR SELECT sJobName, StartDot, EndDot FROM #Chart ORDER BY StartDot, EndDot DESC OPEN cur FETCH cur INTO @sJobName, @StartDot, @EndDot WHILE @@FETCH_STATUS = 0 BEGIN SELECT @sLine = @sJobName + REPLICATE(' ', @nMaxJobNameLen - LEN(@sJobName)) + ' !' + REPLICATE(' ', @StartDot) + '|' + CASE WHEN @EndDot - @StartDot - LEN('||') > 0 THEN REPLICATE('-', @EndDot - @StartDot - LEN('||')) ELSE '-' END + '|' SET @sChart = @sChart + CHAR(10) + @sLine FETCH cur INTO @sJobName, @StartDot, @EndDot END CLOSE cur DEALLOCATE cur DROP TABLE #Chart ------------------------------------------------------------------------------- -- Add top and bottom borders, and the date range, to the chart. ------------------------------------------------------------------------------- SET @sChart = REPLICATE('=', LEN(@sLine) + 1) + @sChart SET @sLine = CAST(@dtEarliest AS NVARCHAR) + REPLICATE(' ', LEN(@SLine) - (2 * LEN('Aug 16 2010 5:05PM')) + 1) + CAST(@dtLatest AS NVARCHAR) SET @sChart = @sChart + char(10) + REPLICATE('=', LEN(@sLine) + 1) + char(10) + @sLine ---------------------------------------------------------------------------------- -- "PRINT" truncates NVARCHAR(MAX) variables at 8000 bytes. So we do this. ---------------------------------------------------------------------------------- DECLARE @CurrentEnd BIGINT -- Track the length of the next substring. DECLARE @offset TINYINT -- Tracks the amount of offset needed. DECLARE @sChartPiece NVARCHAR(MAX) SET @sChartPiece = REPLACE(REPLACE(@sChart, CHAR(13) + CHAR(10), CHAR(10)), CHAR(13), CHAR(10)) WHILE LEN(@sChartPiece) > 1 BEGIN IF CHARINDEX(CHAR(10), @sChartPiece) BETWEEN 1 AND 4000 BEGIN SET @CurrentEnd = CHARINDEX(CHAR(10), @sChartPiece) - 1 SET @offset = 2 END ELSE BEGIN SET @CurrentEnd = 4000 SET @offset = 1 END PRINT SUBSTRING(@sChartPiece, 1, @CurrentEnd) SET @sChartPiece = SUBSTRING(@sChartPiece, @CurrentEnd+@offset, 1073741822) -- 0x3FFFFFF END ------------------------------------------------------------------------------- -- Send me an email. ------------------------------------------------------------------------------- DECLARE @EmailImportance NVARCHAR(6) SET @EmailImportance = 'Normal' DECLARE @EMailSubject NVARCHAR(255) SET @EMailSubject = 'Daily Job History Chart' DECLARE @EmailBody NVARCHAR(MAX) SET @EmailBody = @sChart DECLARE @EmailItemID INT SET @EmailItemID = 0 DECLARE @ProfileId INT SET @ProfileId = -1 DECLARE @ProfileName sysname SET @ProfileName = 'YourProfile' DECLARE @nErr INT SET @nErr = @@ERROR EXEC msdb.dbo.sp_send_dbmail @recipients = 'president@whitehouse.gov', @body = @EmailBody, @body_format = 'TEXT', @subject = @EMailSubject, @importance = @EmailImportance, @profile_name = @ProfileName, @mailitem_id = @EmailItemID OUTPUT -- Check the results of trying to send the mail. SET @nErr = @@ERROR IF @EmailItemID != 0 BEGIN RAISERROR ('sp_send_dbmail - @EmailItemID %d', 10, 1, @EmailItemID) WITH NOWAIT, LOG END IF @nErr != 0 BEGIN RAISERROR ('sp_send_dbmail - @@ERROR %d', 16, 1, @nErr) WITH NOWAIT, LOG END
Here's some example results. (This data is entirely made up - I don't really rebuild indexes that often.) Primitive to be sure, but still makes it easy to spot five jobs running at the same time!
================================================================================================== CHECKDB ! |--| Reorg Indexes ! |-| CHECKDB ! |---| Reorg Indexes ! |-| CHECKDB ! |---| Reorg Indexes ! |-| Reorg Indexes ! |-| Rebuild Indexes ! |-----------------------------------------------------------| CHECKDB ! |--| Reorg Indexes ! |-| CHECKDB ! |---| Reorg Indexes ! |-| CHECKDB ! |--| Reorg Indexes ! |-| CHECKDB ! |------| CHECKDB ! |--| Reorg Indexes ! |----| Daily Reboot ! |--| CHECKDB ! |--------------| Reorg Indexes ! |-| CHECKDB ! |---| Reorg Indexes ! |-| CHECKDB ! |---| Reorg Indexes ! |-| Reorg Indexes ! |-| Load AR data ! |-| CHECKDB ! |-----| Reorg Indexes ! |-| Run Reports ! |-| Load AR data ! |-| Reorg Indexes ! |-| CHECKDB ! |---| Reorg Indexes ! |-| ================================================================================================== Aug 16 2010 10:49PM Aug 17 2010 10:49PM
0 comments:
Post a Comment