Turbo LogShip 1.0 Released

Earlier this year, I wrote a long post about log shipping. One of the key points I mentioned was how slow restoring can be for a read only log shipped database. If going and reading the whole thing is too much effort for you now, I’ll save you the effort (you child of the internet you) and tell you it’s because the database needs to be kept transactionally consistent in between restores when making it available for read-only. It creates a .tuf file (transaction undo file) to keep the progress of all the pages that have uncommitted transactions saved to them. The checking, creation, applying and re-applying of this can take some time where there is a significant amount of uncommitted transactions within a log backup. Continue reading “Turbo LogShip 1.0 Released”

Check on Long Running SQL Agent Job

Although SQL Agent Jobs can be configured to alert users when a job has passed on failed, there is no mechanism to alert users if the job has been running a long time. So I wrote a stored procedure that can be executed using a SQL Agent Job (ironically.) I’ve written it so that you can check individual jobs as opposed to all jobs on a server, as undoubtedly there are some job that I’m not too bothered about if they run longer than others (e.g. log backup durations generally alter depending on the load of the server.) Continue reading “Check on Long Running SQL Agent Job”

SQL Agent Jobs by Average Run Duration

SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dbo schema in the msdb database, so querying for the data quite straightforward.

This week I am posting a query a day that I have written to get the average duration of the jobs run. The real kicker about getting this information is the run_duration column in the sysjobhistory table: the column itself is an int, so it takes a fair bit of data manipulation to retrieve the data in an accurate and legible format.

The query below returns both the duration and the start time of a job within the past 24 hours. This is useful in determining any long running jobs over the past 24 hours, which helped me when I was trying to locate a job that was generating a lot of transactions and causing the log backups to grow quite large. The reason this was a problem for me was that I had a log shipping database that was used for reporting on, and the creation of the transaction undo file (tuf file) in between each backup with STANDBY was causing the restore job to take a lot longer than normal. I’ll pick up on how to fix this issue in a later post, as the reporting database needed to be back up and online with as little down time as possible.

SELECT DISTINCT job_name = NAME
,db_run = rs.db
,run_duration = CONVERT(CHAR(20), DATEADD(second, rd, 0), 108)
,run_datetime = CONVERT (CHAR (50), rundatetime, 108)
FROM (
SELECT j.NAME
,rd = DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR (20), run_duration), 6), 5, 0, ':'), 3, 0, ':'))
,rundatetime = STUFF (STUFF (CONVERT(VARCHAR (20), h.run_date), 5, 0, '-'), 8, 0, '-') + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR (20), run_time), 6), 5, 0, ':'), 3, 0, ':')
,db = s.database_name
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE h.step_id = 0
and run_duration >= -1
GROUP BY j.NAME,s.database_name, s.step_name, run_duration, run_date, run_time
)
AS rs
WHERE CONVERT (CHAR (50), rundatetime, 108) > DATEADD (HOUR, -24, GETDATE())
AND rs.db IS NOT NULL
ORDER BY run_datetime desc

 

SQL Agent Jobs by Average Run Duration and Number of Runs

This is the third post this week on querying the SQL Agent Job tables to return data not included in the GUI.

SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dbo schema in the msdb database, so querying for the data quite straightforward.

This week I am posting a query a day that I have written to get the average duration of the jobs run. The real kicker about getting this information is the run_duration column in the sysjobhistory table: the column itself is an int, so it takes a fair bit of data manipulation to retrieve the data in an accurate and legible format.

The first query returns the jobs by average duration. With this information you can then scroll through the history of a job in the GUI to see if any were significantly higher or lower than average. The next step in this query would be to return any jobs and the dates where the duration was significantly higher than the average. The RedGate Monitor Tool has such a feature, and it is quite handy.

-- avg duration by date including number of runs
SELECT job_name = rs.NAME
	,totes = NumberOfRuns
	,RunDate = rs.run_date
	,avg_ss = rd
	,avg_hhmmss = CONVERT(CHAR(8), DATEADD(second, rd, 0), 108)
FROM (
	SELECT j.NAME
		,h.run_date
		,COUNT(*) AS NumberOfRuns
		,rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 0, ':'), 3, 0, ':')))
	FROM msdb.dbo.sysjobhistory h
	INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
	WHERE h.step_id = 0
		AND CONVERT(DATETIME, convert(CHAR(8), h.run_date)) > DATEADD(DAY, - 7, GETDATE())
	GROUP BY j.NAME
		,h.run_date
	) AS rs
ORDER BY job_name
	,RunDate DESC

 

SQL Agent Jobs by Average Run Duration and Date

Following on from yesterdays post, this is the second query that covers the SQL Agents job duration.

SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dbo schema in the msdb database, so querying for the data quite straightforward.

This week I am posting a query a day that I have written to get the average duration of the jobs run. The real kicker about getting this information is the run_duration column in the sysjobhistory table: the column itself is an int, so it takes a fair bit of data manipulation to retrieve the data in an accurate and legible format.

The first query returns the jobs by average duration and date. With this information you can then scroll through the history of a job in the GUI to see if any were significantly higher or lower than average. The next step in this query would be to return any jobs and the dates where the duration was significantly higher than the average. The RedGate Monitor Tool has such a feature, and it is quite handy.

-- avg duration by date
SELECT job_name = rs.NAME
	,RunDate = rs.run_date
	,avg_ss = rd
	,avg_hhmmss = CONVERT(CHAR(8), DATEADD(second, rd, 0), 108)
FROM (
	SELECT j.NAME
		,h.run_date
		,COUNT(*) AS NumberOfRuns
		,rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 0, ':'), 3, 0, ':')))
	FROM msdb.dbo.sysjobhistory h
	INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
	WHERE h.step_id = 0
		AND CONVERT(DATETIME, convert(CHAR(8), h.run_date)) > DATEADD(DAY, - 7, GETDATE())
	GROUP BY j.NAME
		,h.run_date
	) AS rs
ORDER BY job_name
	,RunDate DESC

 

 

SQL Agent Jobs by Average Run Duration

SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dbo schema in the msdb database, so querying for the data quite straightforward.

This week I am posting a query a day that I have written to get the average duration of the jobs run. The real kicker about getting this information is the run_duration column in the sysjobhistory table: the column itself is an int, so it takes a fair bit of data manipulation to retrieve the data in an accurate and legible format.

The first query returns the jobs by average duration. With this information you can then scroll through the history of a job in the GUI to see if any were significantly higher or lower than average. The next step in this query would be to return any jobs and the dates where the duration was significantly higher than the average. The RedGate Monitor Tool has such a feature, and it is quite handy.

SELECT job_name = NAME
	,totes = NumberOfRuns
	,avg_ss = rd
	,avg_hhmmss = CONVERT(CHAR(8), DATEADD(second, rd, 0), 108)
FROM (
	SELECT j.NAME
		,COUNT(*) AS NumberOfRuns
		,rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 0, ':'), 3, 0, ':'))) < span class = "im" >
	FROM msdb.dbo.sysjobhistory h
	INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
	WHERE h.step_id = 0
	GROUP BY j.NAME
	) AS rs
ORDER BY job_name

 

Get Run Duration of a SQL Agent Job

The MSDB system database holds all the information for any SQL Agent jobs set up on the server. But it is not straightforward to query the system tables to get information like the duration of jobs out of the tables in the correct format. With that in mind, here’s a little script that will return the total duration of the SQL Agent jobs. There are a couple of “WHERE” statements you can include or exclude if you want to include a specific job, or if you want to include all the steps of the jobs.

SELECT job_name AS JobName
	,run_datetime AS DateOfRun
	,run_duration AS RunDuration
FROM (
	SELECT job_name
		,run_datetime
		,SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' + SUBSTRING(run_duration, 5, 2) AS run_duration
	FROM (
		SELECT DISTINCT j.NAME AS job_name
			,run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4
			,run_duration = RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6)
		FROM msdb..sysjobhistory h
		INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
		WHERE step_id = 0-- comment this out to get break down of each step
		) t
	) t
--where job_name = 'add job name here'
ORDER BY job_name
	,run_datetime DESC