May 30, 2016 at 6:46 am
I am trying to pull details and send to email with HTML format.
Requirement is to report details of the SQL Agent Jobs or other jobs or processes that runs more than 12 hours and 24 hours based on the expected job run.
I tried to create table and store data using DMVs and same details sent to email in HTML format.
But, I failed to get the report in HTML format and other ways too.
Is there anyway if someone already planned ??
It is something like BaselineActivity.
Thanks much in advance.
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 30, 2016 at 12:52 pm
I've been using this report technique for years. Mind you, I have heavily modified it for my own purposes but it's a fantastic place to start.
http://www.wisesoft.co.uk/articles/dba_daily_checks_email_report.aspx
May 30, 2016 at 3:44 pm
See "Example C" at the following link. If that doesn't give you some good ideas, then post back.
EDIT... had walked away and didn't realize that YB751 had posted. And, I forgot the link. So, here's the link with the idea that it's a simple/basic study guide on the technique hat's been greatly expanded at the link that YB posted. Again... see "Example C".
https://msdn.microsoft.com/en-us/library/ms190307.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2016 at 7:30 pm
yb751 (5/30/2016)
I've been using this report technique for years. Mind you, I have heavily modified it for my own purposes but it's a fantastic place to start.http://www.wisesoft.co.uk/articles/dba_daily_checks_email_report.aspx
In the article the query to populate @backuplog is written probably in the worst way possible.
So many correlated subqueries, each of them doing another full table scan....
Brrrrrrr!!!
Simple cross-tab query will return the same result, but without all those overheads:
SELECT T.server_name, T.database_name, T.LastFullBackup, datediff(dd,LastFullBackup, GETDATE()) FullDays,
T.LastDiffBackup, datediff(dd,LastDiffBackup, GETDATE()) DiffDays,
T.LastTranBackup ,datediff(hh,LastTranBackup, GETDATE()) TranHours
FROM (
SELECT server_name, database_name,
MAX(CASE BackupType WHEN 'D' THEN LastBackupDate ELSE NULL END ) LastFullBackup,
MAX(CASE BackupType WHEN 'I' THEN LastBackupDate ELSE NULL END ) LastDiffBackup,
MAX(CASE BackupType WHEN 'L' THEN LastBackupDate ELSE NULL END ) LastTranBackup
FROM (SELECT b2.server_name, b2.database_name, b2.type BackupType, MAX(backup_finish_date) LastBackupDate
FROM msdb..backupset b2
GROUP BY b2.server_name, b2.database_name, b2.type
) LB
GROUP BY server_name, database_name
) T
_____________
Code for TallyGenerator
May 31, 2016 at 3:18 am
Greetings All,
Thank You very much for your replies.
I am still in testing state and I am following the links that are pasted.
I will be back with the results and tests back.
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 31, 2016 at 3:29 am
Here is what that I am using from one of the sources. So that, everyone gets benefited.
I modified many points all over, but still I am unable to get the accurate report and also I didnt received report in HTML Format.
I am not a good DEVELOPER, hence I am looking to get it corrected and tesT.
Can anyone help me in getting the below query and HTML formatted lines corrected.
----------------------------------------------------------------------------------------------------------------
/***** CREATE TABLE **********/
---select * from [SQLBslne_LngRunPrcss]
CREATE TABLE [dbo].[SQLBslne_LngRunPrcss](
[ID] [int] IDENTITY(1,1) NOT NULL,
[JobName] [sysname] NOT NULL,
[JobID] [uniqueidentifier] NOT NULL,
[StartExecutionDate] [datetime] NULL,
[AvgDurationMin] [int] NULL,
[DurationLimit] [int] NULL,
[CurrentDuration] [int] NULL,
[RowInsertDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SQLBslne_LngRunPrcss]
ADD CONSTRAINT [DF_SQLBslne_LngRunPrcss_Date] DEFAULT(getdate())
FOR [RowInsertDate]
--------------------------------------------------------------------------------------------------------------
/***** CREATE SP TO GET THE INFO LOGGED **********/
ALTER PROCEDURE [dbo].[usp_SQLBslne_LngRunPrcss]
AS
--Set Mail Profile
DECLARE @MailProfile VARCHAR(50)
SET @MailProfile = (
'SQLServer_2016_SendEmail'
) --Replace with your mail profile name
--Set Email Recipients
DECLARE @MailRecipients VARCHAR(50)
SET @MailRecipients = 'kvcs@dbaalerts.com'
--Set limit in minutes (applies to all jobs)
--NOTE: Percentage limit is applied to all jobs where average runtime greater than 5 minutes
--else the time limit is simply average + 10 minutes
DECLARE @JobLimitPercentage FLOAT
SET @JobLimitPercentage = 150 --Use whole percentages greater than 100
-- Create intermediate work tables for currently running jobs
DECLARE @currently_running_jobs TABLE (
job_id UNIQUEIDENTIFIER NOT NULL
,last_run_date INT NOT NULL
,last_run_time INT NOT NULL
,next_run_date INT NOT NULL
,next_run_time INT NOT NULL
,next_run_schedule_id INT NOT NULL
,requested_to_run INT NOT NULL
,-- BOOL
request_source INT NOT NULL
,request_source_id SYSNAME COLLATE database_default NULL
,running INT NOT NULL
,-- BOOL
current_step INT NOT NULL
,current_retry_attempt INT NOT NULL
,job_state INT NOT NULL
) -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
--Capture Jobs currently working
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
--Temp table exists check
IF OBJECT_ID('tempdb..##RunningJobs') IS NOT NULL
DROP TABLE ##RunningJobs
CREATE TABLE ##RunningJobs (
[JobID] [UNIQUEIDENTIFIER] NOT NULL
,[JobName] [sysname] NOT NULL
,[StartExecutionDate] [DATETIME] NOT NULL
,[AvgDurationMin] [INT] NULL
,[DurationLimit] [INT] NULL
,[CurrentDuration] [INT] NULL
)
INSERT INTO ##RunningJobs (
JobID
,JobName
,StartExecutionDate
,AvgDurationMin
,DurationLimit
,CurrentDuration
)
SELECT jobs.Job_ID AS JobID
,jobs.NAME AS JobName
,act.start_execution_date AS StartExecutionDate
,AVG(FLOOR(run_duration / 100)) AS AvgDurationMin
,CASE
--If job average less than 5 minutes then limit is avg+10 minutes
WHEN AVG(FLOOR(run_duration / 100)) <= 5
THEN (AVG(FLOOR(run_duration / 100))) + 10
--If job average greater than 5 minutes then limit is avg*limit percentage
ELSE (AVG(FLOOR(run_duration / 100)) * (@JobLimitPercentage / 100))
END AS DurationLimit
,DATEDIFF(MI, act.start_execution_date, GETDATE()) AS [CurrentDuration]
FROM @currently_running_jobs crj
INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
INNER JOIN msdb..sysjobactivity AS act ON act.job_id = crj.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL
INNER JOIN msdb..sysjobhistory AS hist ON hist.job_id = crj.job_id
AND hist.step_id = 0
WHERE crj.job_state = 1
GROUP BY jobs.job_ID
,jobs.NAME
,act.start_execution_date
,DATEDIFF(MI, act.start_execution_date, GETDATE())
HAVING CASE
WHEN AVG(FLOOR(run_duration / 100)) <= 5
THEN (AVG(FLOOR(run_duration / 100))) + 10
ELSE (AVG(FLOOR(run_duration / 100)) * (@JobLimitPercentage / 100))
END < DATEDIFF(MI, act.start_execution_date, GETDATE())
--Checks to see if a long running job has already been identified so you are not alerted multiple times
IF EXISTS (
SELECT RJ.*
FROM ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
SELECT CHECKSUM(JobID, StartExecutionDate)
FROM dbo.SQLBslne_LngRunPrcss
)
)
--Send email with results of long-running jobs
EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile
,@recipients = @MailRecipients
,@query = 'USE DBAdmin; Select RJ.*
From ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate) NOT IN (Select CHECKSUM(JobID,StartExecutionDate) From dbo.SQLBslne_LngRunPrcss) '
,@body = 'View attachment to view long running jobs'
,@subject = 'Long Running SQL Agent Job Alert'
,@attach_query_result_as_file = 1;
--Populate SQLBslne_LngRunPrcss table with jobs exceeding established limits
INSERT INTO [dbo].[SQLBslne_LngRunPrcss] (
[JobID]
,[JobName]
,[StartExecutionDate]
,[AvgDurationMin]
,[DurationLimit]
,[CurrentDuration]
) (
SELECT RJ.* FROM ##RunningJobs RJ WHERE CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
SELECT CHECKSUM(JobID, StartExecutionDate)
FROM dbo.SQLBslne_LngRunPrcss
)
)
DROP TABLE ##RunningJobs
GO
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 31, 2016 at 3:37 am
one more query to send report in HTML
DECLARE @xml NVARCHAR(max)
DECLARE @body NVARCHAR(max)
-- specify long running query duration threshold
DECLARE @longrunningthreshold int
SET @longrunningthreshold=1
-- step 1: collect long running query details.
;WITH cte
AS (SELECT [Session_id]=spid,
[Sessioin_start_time]=(SELECT start_time
FROM sys.dm_exec_requests
WHERE spid = session_id),
[Session_status]=Ltrim(Rtrim([status])),
[Session_Duration]=Datediff(mi, (SELECT start_time
FROM sys.dm_exec_requests
WHERE spid = session_id),
Getdate()
),
[Session_query] = Substring (st.text, ( qs.stmt_start / 2 ) + 1,
( ( CASE qs.stmt_end
WHEN -1
THEN
Datalength(st.text)
ELSE qs.stmt_end
END
-
qs.stmt_start ) / 2 ) +
1)
FROM sys.sysprocesses qs
CROSS apply sys.Dm_exec_sql_text(sql_handle) st)
-- step 2: generate html table
SELECT @xml = Cast((SELECT session_id AS 'td',
'',
session_duration AS 'td',
'',
session_status AS 'td',
'',
[session_query] AS 'td'
FROM cte
WHERE session_duration >= @longrunningthreshold
FOR xml path('tr'), elements) AS NVARCHAR(max))
[font="Comic Sans MS"]-- step 3: do rest of html formatting -- this is not working for me.. If I can get the corrected format for HTML, then I think I am done. I have
SET @body =
'<html><body><H2>Long Running Queries ( Limit > 1 Minute ) </H2>< table border = 1 BORDERCOLOR="Black"> < tr>< th align="centre"> Session_id </th> <th> Session_Duration(Minute) </th> <th> Session_status </th> <th> Session_query </th></tr>'
SET @body = @body + @xml + '</table></body></html>'[/font]
-- step 4: send email if a long running query is found.
IF( @xml IS NOT NULL )
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'SQLServer_2016_SendEmail',
@body = @body,
@body_format ='html',
@recipients = 'kvcs@dbareports.com',
@subject = 'ALERT: Long Running Queries'
END
Cheers,
- Win
"Dont Judge a Book by its Cover"
June 1, 2016 at 5:12 am
Appreciate any help on this
Cheers,
- Win
"Dont Judge a Book by its Cover"
June 1, 2016 at 7:06 am
Sergiy (5/30/2016)
yb751 (5/30/2016)
I've been using this report technique for years. Mind you, I have heavily modified it for my own purposes but it's a fantastic place to start.http://www.wisesoft.co.uk/articles/dba_daily_checks_email_report.aspx
In the article the query to populate @backuplog is written probably in the worst way possible.
So many correlated subqueries, each of them doing another full table scan....
Brrrrrrr!!!
Simple cross-tab query will return the same result, but without all those overheads:
SELECT T.server_name, T.database_name, T.LastFullBackup, datediff(dd,LastFullBackup, GETDATE()) FullDays,T.LastDiffBackup, datediff(dd,LastDiffBackup, GETDATE()) DiffDays,
T.LastTranBackup ,datediff(hh,LastTranBackup, GETDATE()) TranHours
FROM (
SELECT server_name, database_name,
MAX(CASE BackupType WHEN 'D' THEN LastBackupDate ELSE NULL END ) LastFullBackup,
MAX(CASE BackupType WHEN 'I' THEN LastBackupDate ELSE NULL END ) LastDiffBackup,
MAX(CASE BackupType WHEN 'L' THEN LastBackupDate ELSE NULL END ) LastTranBackup
FROM (SELECT b2.server_name, b2.database_name, b2.type BackupType, MAX(backup_finish_date) LastBackupDate
FROM msdb..backupset b2
GROUP BY b2.server_name, b2.database_name, b2.type
) LB
GROUP BY server_name, database_name
) T
Well I did mention I had heavily modified it. 😉
Some parts I had stripped out, other places I have added to it, etc... It's a very old article but at the time I found it, it was very helpful and showing how you can generate an html report for just about anything your want. The web guys here will tell ya that (html) tables are bad...but they are perfect for this kind of stuff. lol
June 1, 2016 at 9:50 am
Thanks for your response.
I will still wait for some real stuff that helps on my query that was used..
Cheers,
- Win
"Dont Judge a Book by its Cover"
June 1, 2016 at 1:56 pm
- Win (6/1/2016)
Thanks for your response.I will still wait for some real stuff that helps on my query that was used..
What "real stuff" do you expect?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2016 at 10:31 pm
Thanks for the reply.
I just wanted to know, what actually was wrong in HTML format.
I modified it to different format but I get results in text in one liner.
Hence, I am waiting and requesting, if query that I pasted will help or not.
Else, any other suggestions are welcome.
Cheers,
- Win
"Dont Judge a Book by its Cover"
June 1, 2016 at 10:40 pm
<< Add on >>
We have some jobs (SSIS packages, gets data from 3 different sources) run for 20 hrs max.
Sometimes, due to load or some other factors, the job hangs and we need to stop it and restart.
The other dependent jobs will fall under threshold.
I planned purging historical data and is functioning fine, but still it is hitting 24 hrs sometimes.
So, I planned to see when job runs more than 12 hrs, it should hit email with report (may be more results), more than 24 hrs (may be less or may not be even a single process or session or job).
Application jobs, weekend, month end, month starting, quarterly, half yearly hits more time than we expected. There might me any reason around it. If I get a report, then I will investigate the facts behind the reasons, from Server level to SQL level.
Thats the moto. This is the approved request from CUSTOMER / VENDOR. SInce, we dont wanna disturb any existing processes since it involved 4 flavors of RDBMS and finally loads data into SQL Server 2014.
Hope my explanation make sense.
Cheers,
- Win
"Dont Judge a Book by its Cover"
June 3, 2016 at 2:14 am
-- step 3: do rest of html formatting -- this is not working for me.. If I can get the corrected format for HTML, then I think I am done. I have
SET @body =
'<html><body><H2>Long Running Queries ( Limit > 1 Minute ) </H2>[highlight="#ffff11"]< table[/highlight] border = 1 BORDERCOLOR="Black"> [highlight="#ffff11"]< tr[/highlight]>[highlight="#ffff11"]< th[/highlight] align="centre"> Session_id </th> <th> Session_Duration(Minute) </th> <th> Session_status </th> <th> Session_query </th></tr>'
SET @body = @body + @xml + '</table></body></html>'
Drop the three spaces
June 15, 2016 at 10:21 am
Sorry for the late reply. Was away for week days.
Thanks for your post and suggestions.
I will test the same and post you reply.
Cheers,
- Win
"Dont Judge a Book by its Cover"
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply