Archive

Posts Tagged ‘msdb’

SQL DBA – Query to check Status of last running SQL Jobs

July 20, 2017 Leave a comment

 
To check the status of current running jobs you can use the “Job Activity Monitor” feature in SSMS, but sometimes opening and refreshing the tool takes time.

Here is a simple query you can run in SSMS or from any custom tool to get the status of current running jobs:
 

–> Query #1: This query uses sysjobs view to get the list of all jobs and sysjobhistory view to get the latest status record.

SELECT 
	j.name AS JobName
	,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */  
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS RunTimeStamp
	,CASE 
		WHEN j.enabled = 1 THEN 'Enabled'  
		ELSE 'Disabled'  
	END JobStatus
	,CASE 
		WHEN jh.run_status = 0 THEN 'Failed'
		WHEN jh.run_status = 1 THEN 'Succeeded'
		WHEN jh.run_status = 2 THEN 'Retry'
		WHEN jh.run_status = 3 THEN 'Cancelled'
		ELSE 'Unknown'  
	END JobRunStatus
FROM msdb.dbo.sysjobs j
OUTER APPLY(
	SELECT TOP 1 * 
	FROM msdb.dbo.sysjobhistory jh
	WHERE jh.job_id = j.job_id AND jh.step_id = 0 
	order by jh.instance_id DESC) jh
where j.name like 'ADF%'
ORDER BY j.name, jh.run_date, jh.run_time

 

–> Query #2; This query uses sysjobs view to get the list of all jobs and sysjobservers view to get the current status of job.

SELECT 
	j.name AS JobName
	,IIF(js.last_run_date > 0, 
		DATETIMEFROMPARTS(js.last_run_date/10000, js.last_run_date/100%100, js.last_run_date%100, 
		js.last_run_time/10000, js.last_run_time/100%100, js.last_run_time%100, 0), 
		NULL) AS RunTimeStamp
	,CASE 
		WHEN j.enabled = 1 THEN 'Enabled'  
		ELSE 'Disabled'  
	END JobStatus
	,CASE 
		WHEN js.last_run_outcome = 0 THEN 'Failed'
		WHEN js.last_run_outcome = 1 THEN 'Succeeded'
		WHEN js.last_run_outcome = 2 THEN 'Retry'
		WHEN js.last_run_outcome = 3 THEN 'Cancelled'
		ELSE 'Unknown'  
	END JobRunStatus
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobservers js on js.job_id = j.job_id
where j.name like 'ADF%'
ORDER BY j.name, js.last_run_date, js.last_run_time

 

And if you noted in both the queries above, I’ve used different way to calculate the RunTimeStamp, first by parsing the rum_time column, second by using DateTimeFromParts() function.

Advertisement
Categories: DBA Stuff Tags: ,

SQL DBA – SQL Agent Job history not showing or vanishing away

July 17, 2017 Leave a comment

 
This happened when we started working on a new SQL Server instance for our DEV environment. The history of SQL jobs was not getting retained after a day or few runs.

I checked on the SQL Agent Properties and found that there are some config values set which were very low:
jobhistory_max_rows = 10000
jobhistory_max_rows_per_job = 100

So I checked on MSDN and found that the max permissible values and set them to 999999 & 49999 respectively.

–> This can also be changed by below T-SQL Query by using the system SP sp_set_sqlagent_properties:

USE [msdb]
GO

EXEC msdb.dbo.sp_set_sqlagent_properties 
	@jobhistory_max_rows=999999, 
	@jobhistory_max_rows_per_job=49999
GO
Categories: DBA Stuff Tags: ,

SQL DBA – Stop multiple running SQL Jobs at once

June 14, 2017 2 comments

 
Just like my previous post on Enable/Disable multiple SQL Jobs [link], here I will show how can we stop multiple jobs running by SQL Agent at once.

–> Although you can very well Stop a SQL Job in SSMS by simple right-clicking on it. But if you have multiple SQL Jobs running, to stop each of them will be a cumbersome and time taking task.

I will show how this can be done by a T-SQL query:

USE msdb;
GO

-- Stop Multiple running SQL Jobs at once:
DECLARE @dynSql NVARCHAR(MAX) = ''

SELECT @dynSql += N' msdb.dbo.sp_stop_job @job_name = ''' + j.name + N'''' 
			+ CHAR(10) + CHAR(13)
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobactivity AS ja 
ON ja.job_id = j.job_id
WHERE ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL
ORDER BY j.name;

PRINT @dynSql;
GO

Simply Copy-Paste the the above Dynamically generated SQL Script and Execute it, it will Stop all Running SQL Jobs at once.

Categories: DBA Stuff, SQL Tips Tags: ,

Custom Query to check Frequency, Interval and Time of multiple Job’s in SQL Server

March 22, 2016 1 comment

In my [previous post] I discussed about the system SPs that we can use to get details of a SQL Job, like its Name, ID, Description, Schedule, etc. But what if you need to check these details of various SQL Jobs?

Here in this post we will see how we check the Job Schedule of various SQL Jobs, like:

1. Enable/Disable
2. Frequency (Once, Daily, Weekly, Monthly, etc.)
3. Interval (Specific time, Seconds, Minutes, Hours, etc.)
4. Start Date and Time
5. Description

… by using some system views, like:

– sysJobs
– sysJobsShedules
– sysSchedules
 

select 
	 'Job ID' = j.job_id
	,'Job Name' = j.name
	,'Job Enabled' = case j.enabled
			when 1 then 'Yes'
			else 'No'
		end
	,'Frequency' = case s.freq_type 
		when 1 then 'One time only'
		when 4 then 'Daily'
		when 8 then 'Weekly'
		when 16 then 'Monthly'
		when 32 then 'Monthly, relative to freq_interval'
		when 64 then 'Runs when the SQL Server Agent service starts'
		when 128 then 'Runs when the computer is idle'
	end
	,'Interval' = case s.freq_subday_type
		when 1 then 'At specified time'
		when 2 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Seconds'
		when 4 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Minutes'
		when 8 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Hours'
	end
	,'Start_Date' = substring(convert(varchar(15),active_start_date),1,4) 
		+ '/' + substring(convert(varchar(15),active_start_date),5,2) + '/' 
		+ substring(convert(varchar(15),active_start_date),7,2)
		+ ' ' + 
		case len(active_start_time)
			when 1 then cast('00:00:0' + right(active_start_time,2) as char(8))
			when 2 then cast('00:00:' + right(active_start_time,2) as char(8))
			when 3 then cast('00:0' 
					+ left(right(active_start_time,3),1)  
					+':' + right(active_start_time,2) as char (8))
			when 4 then cast('00:' 
					+ left(right(active_start_time,4),2)  
					+':' + right(active_start_time,2) as char (8))
			when 5 then cast('0' 
					+ left(right(active_start_time,5),1) 
					+':' + Left(right(active_start_time,4),2)  
					+':' + right(active_start_time,2) as char (8))
			when 6 then cast(Left(right(active_start_time,6),2) 
					+':' + Left(right(active_start_time,4),2)  
					+':' + right(active_start_time,2) as char (8))
		end
	,'Schedule Enabled' = case s.enabled
            when 1 then 'Yes'
            else 'No'
        end
    ,'Schedule Desc' = s.name
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobschedules js
on j.job_id = js.job_id
inner join msdb.dbo.sysschedules s
on s.schedule_id = js.schedule_id
order by j.name
GO

SQL Jobs 03
 

I’ve created the above query by collating references form forums and MSDN BoL, you can tweak and modify this query as well as per your requirements.

Categories: SQL Tips Tags: , , , ,

SQL Tips – Search and list out SQL Jobs containing specific word, text or SQL Query

December 12, 2013 Leave a comment

 
Today while working on one database migration project I wanted to check a particular Stored Procedure is called by what all SQL Jobs.

So, I created following queries to pass the SP name in the where clause and get respective SQL Job name.

The 1st query just gives the SQL Job name, and the 2nd query gives the SQL Job name with Job Step name, SQL Command used in that step, and target Database.

use msdb
go

-- #1. List out all SQL Jobs:

select distinct j.name
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
where js.command like '%spStartSystem%'
order by j.name


-- #2. List out all SQL Jobs with SQL command, Step name and target Database:

select 
	j.name, 
	js.step_name, 
	js.command, 
	js.database_name
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
where js.command like '%spStartSystem%'
order by j.name
Categories: SQL Tips Tags: , ,