Skip to content

Commit aaeee55

Browse files
author
Dan Denney
committed
Initial Commit
0 parents  commit aaeee55

327 files changed

Lines changed: 116086 additions & 0 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎!QUICK STATS 2012.sql‎

Lines changed: 639 additions & 0 deletions
Large diffs are not rendered by default.

‎!QUICK STATS.sql‎

Lines changed: 405 additions & 0 deletions
Large diffs are not rendered by default.

‎ALTER USER WITH LOGIN.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
alter user [CORP\CSAN0AdminSoftGrid] with login = [CORP\CSAN0AdminSoftGrid]
2+
3+
4+

‎Agent/Agent - Failed Jobs.sql‎

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
USE [msdb]
2+
GO
3+
/* This code selects the top 10 most recent SQLAgent jobs that failed to
4+
complete successfully and where the email notification failed too.
5+
Jonathan Allen Jul 2012 */
6+
DECLARE @Date DATETIME
7+
SELECT @Date = DATEADD(d, DATEDIFF(d, '19000101', GETDATE()) - 1, '19000101')
8+
SELECT TOP 10
9+
[s].[name] ,
10+
[sjh].[step_name] ,
11+
[sjh].[sql_message_id] ,
12+
[sjh].[sql_severity] ,
13+
[sjh].[message] ,
14+
[sjh].[run_date] ,
15+
[sjh].[run_time] ,
16+
[sjh].[run_duration] ,
17+
[sjh].[operator_id_emailed] ,
18+
[sjh].[operator_id_netsent] ,
19+
[sjh].[operator_id_paged] ,
20+
[sjh].[retries_attempted]
21+
FROM [dbo].[sysjobhistory] AS sjh
22+
INNER JOIN [dbo].[sysjobs] AS s
23+
ON [sjh].[job_id] = [s].[job_id]
24+
WHERE EXISTS ( SELECT *
25+
FROM [dbo].[sysjobs] AS s
26+
INNER JOIN [dbo].[sysjobhistory] AS s2
27+
ON [s].[job_id] = [s2].[job_id]
28+
WHERE [sjh].[job_id] = [s2].[job_id]
29+
AND [s2].[message] LIKE '%failed to notify%'
30+
AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) >= @date
31+
AND [s2].[run_status] = 0 )
32+
AND sjh.[run_status] = 0
33+
AND sjh.[step_id] != 0
34+
AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [run_date])) >= @date
35+
ORDER BY [sjh].[run_date] DESC ,
36+
[sjh].[run_time] DESC
37+
go
38+
39+
USE [msdb]
40+
go
41+
/* This code summarises details of SQLAgent jobs that failed to complete successfully
42+
and where the email notification failed too.
43+
Jonathan Allen Jul 2012 */
44+
DECLARE @Date DATETIME
45+
SELECT @Date = DATEADD(d, DATEDIFF(d, '19000101', GETDATE()) - 1, '19000101')
46+
SELECT [s].name ,
47+
[s2].[step_id] ,
48+
CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) AS [rundate] ,
49+
COUNT(*) AS [execution count]
50+
FROM [dbo].[sysjobs] AS s
51+
INNER JOIN [dbo].[sysjobhistory] AS s2
52+
ON [s].[job_id] = [s2].[job_id]
53+
WHERE [s2].[message] LIKE '%failed to notify%'
54+
AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) >= @date
55+
AND [s2].[run_status] = 0
56+
GROUP BY name ,
57+
[s2].[step_id] ,
58+
[s2].[run_date]
59+
ORDER BY [s2].run_date DESC

‎Agent/Agent Jobs - Schedules and Disable.sql‎

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,106 @@
1+
DECLARE @weekDay TABLE (
2+
mask INT
3+
, maskValue VARCHAR(32)
4+
);
5+
6+
INSERT INTO @weekDay
7+
SELECT 1, 'Sunday' UNION All
8+
SELECT 2, 'Monday' UNION All
9+
SELECT 4, 'Tuesday' UNION All
10+
SELECT 8, 'Wednesday' UNION All
11+
SELECT 16, 'Thursday' UNION All
12+
SELECT 32, 'Friday' UNION All
13+
SELECT 64, 'Saturday';
14+
15+
WITH myCTE
16+
AS(
17+
SELECT sched.name AS 'scheduleName'
18+
, sched.schedule_id
19+
, jobsched.job_id
20+
, CASE WHEN sched.freq_type = 1 THEN 'Once'
21+
WHEN sched.freq_type = 4
22+
And sched.freq_interval = 1
23+
THEN 'Daily'
24+
WHEN sched.freq_type = 4
25+
THEN 'Every ' + CAST(sched.freq_interval AS VARCHAR(5)) + ' days'
26+
WHEN sched.freq_type = 8 THEN
27+
REPLACE( REPLACE( REPLACE((
28+
SELECT maskValue
29+
FROM @weekDay AS x
30+
WHERE sched.freq_interval & x.mask <> 0
31+
ORDER BY mask FOR XML Raw)
32+
, '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '')
33+
+ CASE WHEN sched.freq_recurrence_factor <> 0
34+
And sched.freq_recurrence_factor = 1
35+
THEN '; weekly'
36+
WHEN sched.freq_recurrence_factor <> 0 THEN '; every '
37+
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' weeks' END
38+
WHEN sched.freq_type = 16 THEN 'On day '
39+
+ CAST(sched.freq_interval AS VARCHAR(10)) + ' of every '
40+
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months'
41+
WHEN sched.freq_type = 32 THEN
42+
CASE WHEN sched.freq_relative_interval = 1 THEN 'First'
43+
WHEN sched.freq_relative_interval = 2 THEN 'Second'
44+
WHEN sched.freq_relative_interval = 4 THEN 'Third'
45+
WHEN sched.freq_relative_interval = 8 THEN 'Fourth'
46+
WHEN sched.freq_relative_interval = 16 THEN 'Last'
47+
END +
48+
CASE WHEN sched.freq_interval = 1 THEN ' Sunday'
49+
WHEN sched.freq_interval = 2 THEN ' Monday'
50+
WHEN sched.freq_interval = 3 THEN ' Tuesday'
51+
WHEN sched.freq_interval = 4 THEN ' Wednesday'
52+
WHEN sched.freq_interval = 5 THEN ' Thursday'
53+
WHEN sched.freq_interval = 6 THEN ' Friday'
54+
WHEN sched.freq_interval = 7 THEN ' Saturday'
55+
WHEN sched.freq_interval = 8 THEN ' Day'
56+
WHEN sched.freq_interval = 9 THEN ' Weekday'
57+
WHEN sched.freq_interval = 10 THEN ' Weekend'
58+
END
59+
+ CASE WHEN sched.freq_recurrence_factor <> 0
60+
And sched.freq_recurrence_factor = 1 THEN '; monthly'
61+
WHEN sched.freq_recurrence_factor <> 0 THEN '; every '
62+
+ CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months' END
63+
WHEN sched.freq_type = 64 THEN 'StartUp'
64+
WHEN sched.freq_type = 128 THEN 'Idle'
65+
END AS 'frequency'
66+
, IsNull('Every ' + CAST(sched.freq_subday_interval AS VARCHAR(10)) +
67+
CASE WHEN sched.freq_subday_type = 2 THEN ' seconds'
68+
WHEN sched.freq_subday_type = 4 THEN ' minutes'
69+
WHEN sched.freq_subday_type = 8 THEN ' hours'
70+
END, 'Once') AS 'subFrequency'
71+
, REPLICATE('0', 6 - LEN(sched.active_start_time))
72+
+ CAST(sched.active_start_time AS VARCHAR(6)) AS 'startTime'
73+
, REPLICATE('0', 6 - LEN(sched.active_end_time))
74+
+ CAST(sched.active_end_time AS VARCHAR(6)) AS 'endTime'
75+
, REPLICATE('0', 6 - LEN(jobsched.next_run_time))
76+
+ CAST(jobsched.next_run_time AS VARCHAR(6)) AS 'nextRunTime'
77+
, CAST(jobsched.next_run_date AS CHAR(8)) AS 'nextRunDate'
78+
FROM msdb.dbo.sysschedules AS sched
79+
Join msdb.dbo.sysjobschedules AS jobsched
80+
ON sched.schedule_id = jobsched.schedule_id
81+
WHERE sched.enabled = 1
82+
)
83+
84+
SELECT job.name AS 'jobName'
85+
, sched.scheduleName
86+
, sched.frequency
87+
, sched.subFrequency
88+
, SUBSTRING(sched.startTime, 1, 2) + ':'
89+
+ SUBSTRING(sched.startTime, 3, 2) + ' - '
90+
+ SUBSTRING(sched.endTime, 1, 2) + ':'
91+
+ SUBSTRING(sched.endTime, 3, 2)
92+
AS 'scheduleTime' -- HH:MM
93+
, SUBSTRING(sched.nextRunDate, 1, 4) + '/'
94+
+ SUBSTRING(sched.nextRunDate, 5, 2) + '/'
95+
+ SUBSTRING(sched.nextRunDate, 7, 2) + ' '
96+
+ SUBSTRING(sched.nextRunTime, 1, 2) + ':'
97+
+ SUBSTRING(sched.nextRunTime, 3, 2) AS 'nextRunDate'
98+
/* Note: the sysjobschedules table refreshes every 20 min,
99+
so nextRunDate may be out of date */
100+
, 'Execute msdb.dbo.sp_update_job @job_id = '''
101+
+ CAST(job.job_id AS CHAR(36)) + ''', @enabled = 0;' AS 'disableScript'
102+
FROM msdb.dbo.sysjobs AS job
103+
Join myCTE AS sched
104+
ON job.job_id = sched.job_id
105+
WHERE job.enabled = 1 -- do not display disabled jobs
106+
ORDER BY nextRunDate;

‎Agent/Jobs - Find text in commands.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
select DISTINCT sj.name as [Job Name], command
2+
from msdb.dbo.sysjobsteps sjs
3+
inner join msdb.dbo.sysjobs sj on sjs.job_id = sj.job_id
4+
where command like '%up_ReportMasterMetricsload%'

‎Agent/Jobs - Long Running.sql‎

Lines changed: 114 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,114 @@
1+
/*=============================================
2+
File: long_running_jobs.sql
3+
4+
Author: Thomas LaRock, http://thomaslarock.com/contact-me/
5+
6+
Summary: This script will check to see if any currently
7+
running jobs are running long.
8+
9+
Variables:
10+
@MinHistExecutions - Minimum number of job runs we want to consider
11+
@MinAvgSecsDuration - Threshold for minimum duration we care to monitor
12+
@HistoryStartDate - Start date for historical average
13+
@HistoryEndDate - End date for historical average
14+
15+
These variables allow for us to control a couple of factors. First
16+
we can focus on jobs that are running long enough on average for
17+
us to be concerned with (say, 30 seconds or more). Second, we can
18+
avoid being alerted by jobs that have run so few times that the
19+
average and standard deviations are not quite stable yet. This script
20+
leaves these variables at 1.0, but I would advise you alter them
21+
upwards after testing.
22+
23+
Returns: One result set containing a list of jobs that
24+
are currently running and are running longer than two standard deviations
25+
away from their historical average. The "Min Threshold" column
26+
represents the average plus two standard deviations.
27+
28+
Date: October 3rd, 2012
29+
30+
SQL Server Versions: SQL2005, SQL2008, SQL2008R2, SQL2012
31+
32+
You may alter this code for your own purposes. You may republish
33+
altered code as long as you give due credit.
34+
35+
THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
36+
OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
37+
LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
38+
FITNESS FOR A PARTICULAR PURPOSE.
39+
40+
=============================================*/
41+
/*
42+
Modified 9/23/2013 by Dan Denney to include the SPID from
43+
dm_exec_sessions.
44+
*/
45+
46+
DECLARE @HistoryStartDate datetime
47+
,@HistoryEndDate datetime
48+
,@MinHistExecutions int
49+
,@MinAvgSecsDuration int
50+
51+
SET @HistoryStartDate = '19000101'
52+
SET @HistoryEndDate = GETDATE()
53+
SET @MinHistExecutions = 1.0
54+
SET @MinAvgSecsDuration = 1.0
55+
56+
DECLARE @currently_running_jobs TABLE (
57+
job_id UNIQUEIDENTIFIER NOT NULL
58+
,last_run_date INT NOT NULL
59+
,last_run_time INT NOT NULL
60+
,next_run_date INT NOT NULL
61+
,next_run_time INT NOT NULL
62+
,next_run_schedule_id INT NOT NULL
63+
,requested_to_run INT NOT NULL
64+
,request_source INT NOT NULL
65+
,request_source_id SYSNAME NULL
66+
,running INT NOT NULL
67+
,current_step INT NOT NULL
68+
,current_retry_attempt INT NOT NULL
69+
,job_state INT NOT NULL
70+
)
71+
72+
--capture details on jobs
73+
INSERT INTO @currently_running_jobs
74+
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
75+
76+
;WITH JobHistData AS
77+
(
78+
SELECT job_id
79+
,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
80+
,secs_duration=run_duration/10000*3600
81+
+run_duration%10000/100*60
82+
+run_duration%100
83+
FROM msdb.dbo.sysjobhistory
84+
WHERE step_id = 0 --Job Outcome
85+
AND run_status = 1 --Succeeded
86+
)
87+
,JobHistStats AS
88+
(
89+
SELECT job_id
90+
,AvgDuration = AVG(secs_duration*1.)
91+
,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
92+
FROM JobHistData
93+
WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
94+
AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101') GROUP BY job_id HAVING COUNT(*) >= @MinHistExecutions
95+
AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
96+
)
97+
SELECT jd.job_id
98+
,es.session_id
99+
,j.name AS [JobName]
100+
,MAX(act.start_execution_date) AS [ExecutionDate]
101+
,AvgDuration AS [Historical Avg Duration (secs)]
102+
,AvgPlus2StDev AS [Min Threshhold (secs)]
103+
FROM JobHistData jd
104+
JOIN JobHistStats jhs on jd.job_id = jhs.job_id
105+
JOIN msdb..sysjobs j on jd.job_id = j.job_id
106+
JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id
107+
JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id
108+
AND act.stop_execution_date IS NULL
109+
AND act.start_execution_date IS NOT NULL
110+
INNER JOIN sys.dm_exec_sessions AS es ON jd.job_id = cast(convert( binary(16), substring(es.program_name , 30, 34), 1) as uniqueidentifier)
111+
WHERE secs_duration > AvgPlus2StDev
112+
AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev
113+
AND crj.job_state = 1
114+
GROUP BY jd.job_id, es.session_id, j.name, AvgDuration, AvgPlus2StDev

‎Agent/Jobs - Script all jobs.sql‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
USE msdb
2+
go
3+
4+
SELECT sj.name
5+
,sj.enabled
6+
,sc.name AS Category_Name
7+
,sj.description
8+
,sl.NAME
9+
--,db_owner(sj.owner_sid)
10+
,sj.notify_level_eventlog
11+
,sj.notify_level_email
12+
,sj.notify_level_netsend
13+
,sj.notify_level_page
14+
,sj.notify_email_operator_id
15+
,so.name AS Notify_Email_Operator
16+
,sj.notify_netsend_operator_id
17+
,sj.notify_page_operator_id
18+
,sj.delete_level
19+
,sj.date_created
20+
,sj.date_modified
21+
,sj.version_number
22+
,sc.category_id
23+
,sc.category_class
24+
,sc.category_type
25+
26+
FROM dbo.sysjobs AS sj
27+
INNER JOIN dbo.syscategories AS sc ON sj.category_id = sc.category_id
28+
INNER JOIN master.dbo.syslogins sl ON sl.sid = sj.owner_sid
29+
LEFT JOIN dbo.sysoperators AS so ON sj.notify_email_operator_id = so.id
30+
ORDER BY sj.ENABLED DESC--, sj.name ASC

‎Agent/Recent Job Failures.sql‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
SELECT A.Name as 'JobName', Sub5.MaxRunDate, Sub5.MaxRunTime, Sub5.MostRecentJobStatus
2+
FROM msdb.dbo.sysJobs A LEFT OUTER JOIN
3+
(SELECT A.Job_ID, Sub4.MaxRunDate, Sub4.MaxRunTime,
4+
CASE A.run_status
5+
WHEN 0 THEN 'Failed'
6+
WHEN 1 THEN 'Successful'
7+
WHEN 2 THEN 'Retry'
8+
WHEN 3 THEN 'Cancelled'
9+
WHEN 4 THEN 'In Progress'
10+
END as 'MostRecentJobStatus'
11+
FROM msdb.dbo.sysJobHistory A INNER JOIN
12+
( SELECT A.Job_ID, Sub3.MaxRunDate, Sub3.MaxRunTime, Sub3.MaxStepID, MAX(A.Instance_ID) as 'MaxInstanceID'
13+
FROM msdb.dbo.sysJobHistory A INNER JOIN
14+
( SELECT A.Job_ID, Sub2.MaxRunDate, Sub2.MaxRunTime, MAX(A.Step_ID) as 'MaxStepID'
15+
FROM msdb.dbo.sysJobHistory A INNER JOIN
16+
( SELECT A.Job_ID, Sub1.MaxRunDate, MAX(A.run_time) as 'MaxRunTime'
17+
FROM msdb.dbo.sysJobHistory A INNER JOIN
18+
( SELECT A.Job_ID, MAX(A.run_date) as 'MaxRunDate'
19+
FROM msdb.dbo.sysJobHistory A
20+
GROUP BY A.Job_ID) Sub1 ON
21+
A.Job_ID = Sub1.Job_ID AND
22+
A.run_date = Sub1.MaxRunDate
23+
GROUP BY A.Job_ID, Sub1.MaxRunDate) Sub2 ON
24+
A.Job_ID = Sub2.Job_ID AND
25+
A.run_date = Sub2.MaxRunDate AND
26+
A.run_time = Sub2.MaxRunTime
27+
GROUP BY A.Job_ID, Sub2.MaxRunDate, Sub2.MaxRunTime) Sub3 ON
28+
A.Job_ID = Sub3.Job_ID AND
29+
A.run_date = Sub3.MaxRunDate AND
30+
A.run_time = Sub3.MaxRunTime AND
31+
A.Step_ID = Sub3.MaxStepID
32+
GROUP BY A.Job_ID, Sub3.MaxRunDate, Sub3.MaxRunTime, Sub3.MaxStepID) Sub4 ON
33+
A.Job_ID = Sub4.Job_ID AND
34+
A.run_date = Sub4.MaxRunDate AND
35+
A.run_time = Sub4.MaxRunTime AND
36+
A.Step_ID = Sub4.MaxStepID AND
37+
A.Instance_ID = Sub4.MaxInstanceID) Sub5 ON
38+
A.Job_ID = Sub5.Job_ID
39+
WHERE A.[Enabled] = 1
40+
and Sub5.MostRecentJobStatus = 'Failed'
41+
ORDER BY A.Name

0 commit comments

Comments
 (0)