select * from [sys].[configurations];
Get jobs that run more then usually (SQL Server)
DECLARE
@MinDelayPercent money = 0.5, -- run X% mare then the average duration
@MinDelaySeconds int = 300 -- minimum delay time =- in order to ignore short jobs delay
-- Get Running Jobs
CREATE TABLE #RunningJobs
(
Job_ID UNIQUEIDENTIFIER,
Last_Run_Date INT,
Last_Run_Time INT,
Next_Run_Date INT,
Next_Run_Time INT,
Next_Run_Schedule_ID INT,
Requested_To_Run INT,
Request_Source INT,
Request_Source_ID VARCHAR(100),
Running INT,
Current_Step INT,
Current_Retry_Attempt INT,
State INT
)
INSERT INTO #RunningJobs EXEC master.dbo.xp_sqlagent_enum_
; with avgDrtn as
(
select h.job_id,
avg ( ( (h.run_duration % 100) -- Seconds
+ 60 * ((h.run_duration /100) % 100) -- + minutes to seconds
+ 3600 * ((h.run_duration / 10000) % 100) -- + Hours to seconds
)
) as AvgJobDuration_Seconds
from msdb.dbo.sysjobhistory h
where h.step_id = 0
group by h.job_id
)
SELECT r.Job_ID, j.name AS JobName,
msdb.dbo.agent_datetime(r.Next
DATEDIFF(second, msdb.dbo.agent_datetime(r.Next
a.AvgJobDuration_Seconds
FROM #RunningJobs r
JOIN msdb.dbo.sysjobs j ON r.Job_ID=j.job_id
JOIN avgDrtn a ON j.job_id = a.job_id
WHERE -- job is still running
r.Running=1
AND -- ignore short jobs delay
( cast(DATEDIFF(second, msdb.dbo.agent_datetime(r.Next
-
cast(a.AvgJobDuration_Seconds as money)
) > @MinDelaySeconds
AND -- X% mare then the average duration
( cast(DATEDIFF(second, msdb.dbo.agent_datetime(r.Next
/
cast(a.AvgJobDuration_Seconds as money)
) > (1+@MinDelayPercent)
--ORDER BY name,next_run_date,next_run_
DROP TABLE #RunningJobs
Get dcheduled jobs and their schedules (SQL Server)
select j.job_id, j.name as JobName, j.[enabled] as JobEnabled,
js.schedule_id,
( 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'
WHEN 64 THEN 'When SQLServer Agent starts'
WHEN 128 THEN 'When the computer is idle'
ELSE ''
END
) Frequently,
( CASE len(s.active_start_time)
WHEN 1 THEN cast('00:00:0' + cast(s.active_start_time as char) as char (8))
WHEN 2 THEN cast('00:00:' + cast(s.active_start_time as char) as char (8))
WHEN 3 THEN cast('00:0' + Left(right(s.active_start_time
WHEN 4 THEN cast('00:' + Left(right(s.active_start_time
WHEN 5 THEN cast('0' + Left(right(s.active_start_time
WHEN 6 THEN cast(Left(right(s.active_
END
) as RunTime
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobschedules js on j.job_id = js.job_id
join msdb.dbo.sysschedules s on js.schedule_id = s.schedule_id
where s.[enabled] = 1
and s.freq_type between 4 and 32
order by s.freq_type, s.active_start_time
This query return the scheduling's of jobs in the server.
Jobs without scheduling won't be return (unless you change the query to LEFT JOINs) – in order to get the scheduling mapping in the server.
Average SQL jobs duration
select h.job_id,
avg (( (h.run_duration % 100) -- Seconds
+ 60 * ((h.run_duration /100) % 100) -- + minutes to seconds
+ 3600 * ((h.run_duration / 10000) % 100) -- + Hours to seconds
)
) as AvgJobDuration_Seconds
from msdb.dbo.sysjobhistory h
where h.step_id = 0
group by h.job_id
Current running SQL jobs
IF OBJECT_ID('tempdb.dbo.#
DROP TABLE #RunningJobs
CREATE TABLE #RunningJobs
(
Job_ID UNIQUEIDENTIFIER,
Last_Run_Date INT,
Last_Run_Time INT,
Next_Run_Date INT,
Next_Run_Time INT,
Next_Run_Schedule_ID INT,
Requested_To_Run INT,
Request_Source INT,
Request_Source_ID VARCHAR(100),
Running INT,
Current_Step INT,
Current_Retry_Attempt INT,
State INT
)
INSERT INTO #RunningJobs
EXEC master.dbo.xp_sqlagent_enum_
SELECT j.job_id, j.name AS JobName,
(CASE WHEN next_run_date=0 THEN '[Not scheduled]' ELSE CONVERT(VARCHAR,msdb.dbo.agent
FROM #RunningJobs r
JOIN msdb.dbo.sysjobs j on r.Job_ID = j.job_id
WHERE r.Running = 1 -- running now
ORDER BY j.name, r.next_run_date, r.next_run_time
IF OBJECT_ID('tempdb.dbo.#
DROP TABLE #RunningJobs
An item with the same key has already been added
Error when try to open an alert properties:
An item with the same key has already been added. (mscorlib)
Cause: There are 2 (or more) jobs with the same name.
Find the duplicated names of jobs using this query:
select name, count(*) from msdb.dbo.sysjobs
group by name having count(*) > 1
Fix: Rename or delete one of the jobs with the duplicate name.
* Alerts can be executed by jobs, so maybe it caused an error when try to find this connection between alerts and jobs.
Get triggers details
SELECT sysobjects.name AS trigger_name,
s.name AS table_schema, OBJECT_NAME(parent_obj) AS table_name,
OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate,
OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete,
OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert,
OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter,
OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof,
OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
JOIN sys.tables t ON sysobjects.parent_obj = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
AND t.name = 'TableName'
Subscribe to:
Posts (Atom)