February 27, 2023 at 11:27 am
Hi,
How can I query from which database that the sql server job is running from?
Thanks.
February 27, 2023 at 12:59 pm
It will heavily depend on the job step type but your best bet is "msdb.dbo.sysjobsteps".
For example a T-SQL step will detail in the database_name column what database the step is set to use (note the actual code may query other databases)
But for PowerShell steps, SSIS steps etc you will need to then go an interrogate what the powershell script/package etc etc is doing outside of SQL.
February 27, 2023 at 3:01 pm
Hi,
How can I query from which database that the sql server job is running from?
Thanks.
What are you asking?
If you are looking for any information about the job, schedule, and execution status, then that is all contained in the msdb database.
If you are looking for what code is being executed from a SQL job, then that's different. You could download sp_whoisactive, and that will tell you if SQL Agent is executing code.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 27, 2023 at 6:06 pm
Check if this helps
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date, DATEDIFF(MINUTE,ja.start_execution_date,getdate()) 'Running Minutes',
ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j on ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;
Regards
Durai Nagarajan
March 1, 2023 at 9:26 pm
I was looking for the info on what DB a job is running. For Example if I am running a stored procedure in the job and if that stored procedure is running in the the database AAAAA I was looking for the job name associated with the database it is running.
I was able to get all the info I was looking with the following script.
USE [msdb];
GO
SELECT
j.[name] AS [Job Name],
s.step_id,
s.[step_name] AS [Step_Name],
s.[database_name] AS [Database Name],
s.[command] AS [SQL Script],
CASE WHEN j.enabled=1 THEN 'yes' ELSE 'No' END as Enabled,
j.description,
c.name JobCategory,
CASE WHEN c.category_class=1 THEN 'Job'
WHEN c.category_class=2 THEN 'Alert'
WHEN c.category_class=3 THEN 'Operator' END as JobCategoryClass,
CASE WHEN c.category_type=1 THEN 'Local'
WHEN c.category_type=2 THEN 'Multiserver'
WHEN c.category_type=3 THEN 'None' END as JobCategoryType
INTO #temp1
FROM [dbo].[sysjobs] AS j
INNER JOIN [dbo].[sysjobsteps] AS s ON j.[job_id] = s.[job_id]
INNER JOIN syscategories c
ON j.category_id=c.category_id
--WHERE j.[enabled] = 1
AND s.[subsystem] = 'TSQL'
ORDER BY j.[name], s.[step_id]
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
,js.step_id
,js.step_name
, CASE
WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(
CAST([sJOBH].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
, CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [LastRunStatus]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
, [sJOBH].[message] AS [LastRunStatusMessage]
, CASE [sJOBSCH].[NextRunDate]
WHEN 0 THEN NULL
ELSE CAST(
CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [NextRunDateTime]
INTO #temp2
FROM
[msdb].[dbo].[sysjobs] AS [sJOB] INNER JOIN msdb.[dbo].[sysjobsteps] js
ON [sJOB].job_id=js.job_id
LEFT JOIN (
SELECT
[job_id]
, MIN([next_run_date]) AS [NextRunDate]
, MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]
) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN (
SELECT
[job_id]
, [run_date]
, [run_time]
, [run_status]
, [run_duration]
, [message]
, ROW_NUMBER() OVER (
PARTITION BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC
) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]
select
sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.freq_recurrence_factor
,case
when freq_type = 4 then 'Daily'
end frequency
,
'every ' + cast (freq_interval as varchar(3)) + ' day(s)' Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
INTO #temp3
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 4
union
-- jobs with a weekly schedule
select
sysjobs.name job_name
,sysjobs.enabled job_enabled
,sysschedules.name schedule_name
,sysschedules.freq_recurrence_factor
,case
when freq_type = 8 then 'Weekly'
end frequency
,
replace
(
CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
,', '
,''
) Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 8
order by job_enabled desc
SELECT @@SERVERNAME as ServerName,a.[Job Name],a.step_id,b.step_name,a.[Database Name],a.Enabled,a.description,a.[SQL Script],
c.frequency,c.Days,c.freq_recurrence_factor,c.time,a.JobCategory,a.JobCategoryClass,a.JobCategoryType,
b.LastRunDateTime,b.[LastRunDuration (HH:MM:SS)],b.LastRunStatus,b.LastRunStatusMessage,b.NextRunDateTime
FROM #temp1 a INNER JOIN #temp2 b ON a.[Job Name]=b.JobName
AND a.step_id=b.step_id LEFT JOIN #temp3 c
ON a.[Job Name]=c.job_name
March 3, 2023 at 10:24 am
No need for a large sql statement. Just open the properties of the job and check which database is uses. Easy peasy.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply