forked from chrisdamba/SQL-Server-Management-Studio
-
Notifications
You must be signed in to change notification settings - Fork 0
/
WhatWasRunning Check sprocs.sql
168 lines (145 loc) · 5.29 KB
/
WhatWasRunning Check sprocs.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
USE [DBAadmin]
GO
EXEC [dbasp_WhatWasRunningBetween] '2014-09-15 17:20:00','2014-09-15 17:30:00',1
EXEC [dbasp_WhatWasRunningAt] '2014-09-15 17:29:00'
USE [DBAadmin]
GO
/****** Object: StoredProcedure [dbo].[dbasp_WhatWasRunningAt] Script Date: 9/16/2014 1:06:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[dbasp_WhatWasRunningAt]
(
@jobsRunningAt DATETIME = NULL
)
AS
WITH JobHistorySummary AS
(
SELECT
jobs.job_id,
job_name = jobs.[name],
step_id,
step_name,
run_time,
run_time_hours = run_time/10000,
run_time_minutes = (run_time%10000)/100,
run_time_seconds = (run_time%10000)%100,
run_time_elapsed_seconds =
(run_time/10000 /*run_time_hours*/ * 60 * 60 /* hours to minutes to seconds*/) +
((run_time%10000)/100 /* run_time_minutes */ * 60 /* minutes to seconds */ ) +
(run_time%10000)%100,
Start_Date = CONVERT(DATETIME, RTRIM(run_date)),
Start_DateTime =
CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60)
+ (run_time%10000)%100 /*run_time_elapsed_seconds*/)
/ (23.999999*3600 /* seconds in a day*/),
End_DateTime =
CONVERT(DATETIME, RTRIM(run_date))
+ ((run_time/10000 * 3600)
+ ((run_time%10000)/100*60)
+ (run_time%10000)%100)
/ (86399.9964 /* Start Date Time */)
+ ((run_duration/10000 * 3600)
+ ((run_duration%10000)/100*60)
+ (run_duration%10000)%100 /*run_duration_elapsed_seconds*/)
/ (86399.9964 /* seconds in a day*/)
FROM msdb.dbo.sysjobs jobs WITH(NOLOCK)
INNER JOIN msdb.dbo.sysjobhistory history WITH(NOLOCK) ON
jobs.job_id = history.job_id
WHERE step_name = '(Job outcome)' --Only interested in final outcome of jobs
)
SELECT 'CHECK TIME' AS [Event]
,COALESCE(@jobsRunningAt,getdate()) AS [DateTime]
UNION ALL
SELECT
'START - ' + job_name AS [Event]
,Start_DateTime AS [DateTime]
FROM JobHistorySummary
WHERE Start_DateTime <= @jobsRunningAt AND End_DateTime >= @jobsRunningAt
UNION ALL
SELECT
'STOP - ' + job_name
,End_DateTime
FROM JobHistorySummary
WHERE Start_DateTime <= COALESCE(@jobsRunningAt,getdate()) AND End_DateTime >= COALESCE(@jobsRunningAt,getdate())
ORDER BY [DateTime];
GO
/****** Object: StoredProcedure [dbo].[dbasp_WhatWasRunningBetween] Script Date: 9/16/2014 12:54:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[dbasp_WhatWasRunningBetween]
(
@WindowStart DateTime = NULL
,@WindowEnd DateTime = NULL
,@ShowSteps Bit = 0
)
AS
SELECT DISTINCT
--instance_id
Job_name
,CASE @ShowSteps WHEN 1 Then step_name else '' END AS step_name
,CASE @ShowSteps WHEN 1 Then step_id else '' END AS step_id
,CASE @ShowSteps WHEN 1 Then SQL_Message_id ELSE '' END AS SQL_Message_id
,CASE @ShowSteps WHEN 1 Then SM.text ELSE '' END AS SQL_Message
,CASE @ShowSteps WHEN 1 Then SQL_severity ELSE '' END AS SQL_severity
,CASE @ShowSteps WHEN 1 Then [status] ELSE '' END AS [status]
,CASE @ShowSteps WHEN 1 Then [message] ELSE '' END AS [message]
,CASE @ShowSteps WHEN 1 Then Start_DateTime ELSE Job_Start_DateTime END AS Start_DateTime
,CASE @ShowSteps WHEN 1 Then End_DateTime ELSE Job_End_DateTime END AS End_DateTime
FROM (
SELECT instance_id,
jobs.job_id,
job_name = jobs.[name],
step_id,
step_name,
SQL_Message_id,
SQL_severity,
[status] = CASE run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In Progress' END,
[message],
run_time,
run_time_hours = run_time/10000,
run_time_minutes = (run_time%10000)/100,
run_time_seconds = (run_time%10000)%100,
run_time_elapsed_seconds =
(run_time/10000 /*run_time_hours*/ * 60 * 60 /* hours to minutes to seconds*/) +
((run_time%10000)/100 /* run_time_minutes */ * 60 /* minutes to seconds */ ) +
(run_time%10000)%100,
Start_Date = CONVERT(DATETIME, RTRIM(run_date)),
Start_DateTime =
dbaadmin.dbo.dbaudf_AgentDateTime2DateTime (run_date,run_time),
End_DateTime =
dbaadmin.dbo.dbaudf_AgentDateTime2DateTime (run_date,run_time+run_duration),
Job_Start_DateTime = (
SELECT TOP 1
dbaadmin.dbo.dbaudf_AgentDateTime2DateTime (T1.run_date,T1.run_time)
FROM msdb.dbo.sysjobhistory T1
WHERE T1.job_id = jobs.job_id
AND T1.Step_id = 0
AND T1.instance_id > history.instance_id
ORDER BY T1.instance_id
),
Job_End_DateTime = (
SELECT TOP 1
dbaadmin.dbo.dbaudf_AgentDateTime2DateTime (T1.run_date,T1.run_time+T1.run_duration)
FROM msdb.dbo.sysjobhistory T1
WHERE T1.job_id = jobs.job_id
AND T1.Step_id = 0
AND T1.instance_id > history.instance_id
ORDER BY T1.instance_id
)
FROM msdb.dbo.sysjobs jobs WITH(NOLOCK)
INNER JOIN msdb.dbo.sysjobhistory history WITH(NOLOCK)
ON jobs.job_id = history.job_id
AND step_id != 0
) Data
LEFT JOIN sys.messages sm
ON sm.message_id = data.SQL_Message_id
AND sm.language_id = 1033
WHERE Job_Start_DateTime <= COALESCE(@WindowEnd,GetDate())
AND Job_End_DateTime >= COALESCE(@WindowStart,GetDate()-.25)
--ORDER BY Instance_id
GO