SELECT
JobName = job.name,
JobOwner = dbp.name,
ScheduleName = sch.Name,
ScheduleOwner = schdbp.name
from msdb.dbo.sysschedules sch
left join msdb.dbo.sysjobschedules AS jobsch ON sch.schedule_id = jobsch.schedule_id
left join msdb.dbo.sysjobs AS job ON jobsch.job_id = job.job_id
--left join msdb.dbo.sysjobsteps AS jobstp ON job.job_id = jobstp.job_id AND job.start_step_id = jobstp.step_id
left join msdb.sys.database_principals AS dbp ON job.owner_sid = dbp.sid
left join msdb.sys.database_principals AS schdbp ON sch.owner_sid = schdbp.sid
left join msdb.dbo.syscategories AS cat ON job.category_id = cat.category_id
where job.name in ('xxx')
--where sch.name like '%Monthly Reporting%'
order by job.name, sch.name
Thursday, May 17, 2018
for the schedule only
SELECT [JobName] = [jobs].[name],
freq_type,
freq_interval,
freq_relative_interval,
active_start_date,
active_end_date
--,[Category] = [categories].[name]
--,[Owner] = SUSER_SNAME([jobs].[owner_sid])
--,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
--,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
--,[Description] = [jobs].[description]
,[Occurs] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPU(s) become idle'
ELSE ''
END
,[Occurs_detail] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'O'
WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' +
LEFT(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END ,
LEN(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END
) - 1
)
WHEN 16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
WHEN 32 THEN 'The ' +
CASE [schedule].[freq_relative_interval]
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END +
CASE [schedule].[freq_interval]
WHEN 1 THEN ' Sunday'
WHEN 2 THEN ' Monday'
WHEN 3 THEN ' Tuesday'
WHEN 4 THEN ' Wednesday'
WHEN 5 THEN ' Thursday'
WHEN 6 THEN ' Friday'
WHEN 7 THEN ' Saturday'
WHEN 8 THEN ' Day'
WHEN 9 THEN ' Weekday'
WHEN 10 THEN ' Weekend Day'
END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
ELSE ''
END
,[Frequency] =
CASE [schedule].[freq_subday_type]
WHEN 1 THEN 'Occurs once at ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 2 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 4 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 8 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
ELSE ''
END
,[AvgDurationInSec] = CONVERT(DECIMAL(18, 2), [jobhistory].[AvgDuration])
,[Next_Run_Date] =
CASE [jobschedule].[next_run_date]
WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
END
FROM [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)
LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)
ON [jobs].[job_id] = [jobschedule].[job_id]
LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)
ON [jobschedule].[schedule_id] = [schedule].[schedule_id]
INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)
ON [jobs].[category_id] = [categories].[category_id]
LEFT OUTER JOIN
( SELECT [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +
(([run_duration] % 10000) / 100 * 60) +
([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
FROM [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
WHERE [step_id] = 0
GROUP BY [job_id]
) AS [jobhistory]
ON [jobhistory].[job_id] = [jobs].[job_id]
--where freq_type = 32 and freq_interval = 8
freq_type,
freq_interval,
freq_relative_interval,
active_start_date,
active_end_date
--,[Category] = [categories].[name]
--,[Owner] = SUSER_SNAME([jobs].[owner_sid])
--,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
--,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
--,[Description] = [jobs].[description]
,[Occurs] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPU(s) become idle'
ELSE ''
END
,[Occurs_detail] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'O'
WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' +
LEFT(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END ,
LEN(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END
) - 1
)
WHEN 16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
WHEN 32 THEN 'The ' +
CASE [schedule].[freq_relative_interval]
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END +
CASE [schedule].[freq_interval]
WHEN 1 THEN ' Sunday'
WHEN 2 THEN ' Monday'
WHEN 3 THEN ' Tuesday'
WHEN 4 THEN ' Wednesday'
WHEN 5 THEN ' Thursday'
WHEN 6 THEN ' Friday'
WHEN 7 THEN ' Saturday'
WHEN 8 THEN ' Day'
WHEN 9 THEN ' Weekday'
WHEN 10 THEN ' Weekend Day'
END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
ELSE ''
END
,[Frequency] =
CASE [schedule].[freq_subday_type]
WHEN 1 THEN 'Occurs once at ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 2 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 4 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 8 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
ELSE ''
END
,[AvgDurationInSec] = CONVERT(DECIMAL(18, 2), [jobhistory].[AvgDuration])
,[Next_Run_Date] =
CASE [jobschedule].[next_run_date]
WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
END
FROM [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)
LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)
ON [jobs].[job_id] = [jobschedule].[job_id]
LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)
ON [jobschedule].[schedule_id] = [schedule].[schedule_id]
INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)
ON [jobs].[category_id] = [categories].[category_id]
LEFT OUTER JOIN
( SELECT [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +
(([run_duration] % 10000) / 100 * 60) +
([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
FROM [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
WHERE [step_id] = 0
GROUP BY [job_id]
) AS [jobhistory]
ON [jobhistory].[job_id] = [jobs].[job_id]
--where freq_type = 32 and freq_interval = 8
query jobs and schedules
SELECT
sSCH.Schedule_id,
--sSCH.Schedule_uid,
sSCH.Name,
sJOB.job_id,
sJOB.name AS JobName,
--sJSTP.command,
--sJOB.Description,
--sDBP.name AS JobOwner,
--sCAT.name AS JobCategory,
--sJOB.description AS JobDescription,
--sJOB.date_created AS JobCreatedOn,
--sJOB.date_modified AS JobLastModifiedOn,
--sSVR.name AS OriginatingServerName,
sJSTP.step_id AS JobStartStepNo,
sJSTP.step_name AS JobStartStepName,
--sJOB.enabled,
--sSCH.active_start_date,
--sSCH.active_start_time,
--sSch.Date_created,
--sSch.Date_modified,
--sSch.Version_number,
sJSTP.database_name,
sJSTP.command
--CASE WHEN sSCH.schedule_uid IS NULL THEN 'No' ELSE 'Yes' END AS IsScheduled,
--sSCH.schedule_uid AS JobScheduleID
--sSCH.name AS JobScheduleName,
--CASE sJOB.delete_level
-- WHEN 0 THEN 'Never'
-- WHEN 1 THEN 'On Success'
-- WHEN 2 THEN 'On Failure'
-- WHEN 3 THEN 'On Completion'
-- END AS JobDeletionCriterion
FROM msdb.dbo.sysjobs AS sJOB
LEFT JOIN msdb.sys.servers AS sSVR ON sJOB.originating_server_id = sSVR.server_id
LEFT JOIN msdb.dbo.syscategories AS sCAT ON sJOB.category_id = sCAT.category_id
LEFT JOIN msdb.dbo.sysjobsteps AS sJSTP ON sJOB.job_id = sJSTP.job_id
--AND sJOB.start_step_id = sJSTP.step_id
LEFT JOIN msdb.sys.database_principals AS sDBP ON sJOB.owner_sid = sDBP.sid
LEFT JOIN msdb.dbo.sysjobschedules AS sJOBSCH ON sJOB.job_id = sJOBSCH.job_id
LEFT JOIN msdb.dbo.sysschedules AS sSCH ON sJOBSCH.schedule_id = sSCH.schedule_id
where sJSTP.command like '%.dtsx%'
sSCH.Schedule_id,
--sSCH.Schedule_uid,
sSCH.Name,
sJOB.job_id,
sJOB.name AS JobName,
--sJSTP.command,
--sJOB.Description,
--sDBP.name AS JobOwner,
--sCAT.name AS JobCategory,
--sJOB.description AS JobDescription,
--sJOB.date_created AS JobCreatedOn,
--sJOB.date_modified AS JobLastModifiedOn,
--sSVR.name AS OriginatingServerName,
sJSTP.step_id AS JobStartStepNo,
sJSTP.step_name AS JobStartStepName,
--sJOB.enabled,
--sSCH.active_start_date,
--sSCH.active_start_time,
--sSch.Date_created,
--sSch.Date_modified,
--sSch.Version_number,
sJSTP.database_name,
sJSTP.command
--CASE WHEN sSCH.schedule_uid IS NULL THEN 'No' ELSE 'Yes' END AS IsScheduled,
--sSCH.schedule_uid AS JobScheduleID
--sSCH.name AS JobScheduleName,
--CASE sJOB.delete_level
-- WHEN 0 THEN 'Never'
-- WHEN 1 THEN 'On Success'
-- WHEN 2 THEN 'On Failure'
-- WHEN 3 THEN 'On Completion'
-- END AS JobDeletionCriterion
FROM msdb.dbo.sysjobs AS sJOB
LEFT JOIN msdb.sys.servers AS sSVR ON sJOB.originating_server_id = sSVR.server_id
LEFT JOIN msdb.dbo.syscategories AS sCAT ON sJOB.category_id = sCAT.category_id
LEFT JOIN msdb.dbo.sysjobsteps AS sJSTP ON sJOB.job_id = sJSTP.job_id
--AND sJOB.start_step_id = sJSTP.step_id
LEFT JOIN msdb.sys.database_principals AS sDBP ON sJOB.owner_sid = sDBP.sid
LEFT JOIN msdb.dbo.sysjobschedules AS sJOBSCH ON sJOB.job_id = sJOBSCH.job_id
LEFT JOIN msdb.dbo.sysschedules AS sSCH ON sJOBSCH.schedule_id = sSCH.schedule_id
where sJSTP.command like '%.dtsx%'
sp_spaceused using SQL query
select
-- from first result set of 'exec sp_spacedused'
DB_NAME() as [database_name],
--DBSize = CAST((sf.dbsize + sf.logsize) / 128.0 AS numeric(20, 2)),
--ltrim(str((convert (dec (15,2),sf.dbsize) + convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as [database_size],
UnallocatedSpace = CAST((CASE WHEN sf.dbsize >= pages.reservedpages THEN (sf.dbsize - pages.reservedpages) / 128.0 ELSE 0 END) AS numeric(20, 2)),
--ltrim(str((case when sf.dbsize >= pages.reservedpages then
-- (convert (dec (15,2),sf.dbsize) - convert (dec (15,2),pages.reservedpages))
-- * 8192 / 1048576 else 0 end),15,2) + ' MB') as [unallocated space],
-- from second result set of 'exec sp_spacedused'
ReservedKB = pages.reservedpages * 8,
--ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) + ' KB') as [reserved],
DataKB = pages.pages * 8,
--ltrim(str(pages.pages * 8192 / 1024.,15,0) + ' KB') as data,
IndexSizeKB = (pages.usedpages - pages.pages) * 8,
--ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) + ' KB') as index_size,
UnusedKB = (pages.reservedpages - pages.usedpages) * 8,
--ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) + ' KB') as unused,
-- additional:
ltrim(str((convert (dec (15,2),sf.dbsize)) * 8192 / 1048576,15,2) + ' MB') as dbsize,
ltrim(str((convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as logsize
FROM
(
select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) as dbsize,
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) as logsize
from dbo.sysfiles (nolock)
) sf,
(
select
sum(a.total_pages) as reservedpages,
sum(a.used_pages) as usedpages,
sum(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) as pages
from sys.partitions p (nolock)
join sys.allocation_units a (nolock) on p.partition_id = a.container_id
left join sys.internal_tables it (nolock) on p.object_id = it.object_id
) pages
--*/
--exec sp_spaceused
-- from first result set of 'exec sp_spacedused'
DB_NAME() as [database_name],
--DBSize = CAST((sf.dbsize + sf.logsize) / 128.0 AS numeric(20, 2)),
--ltrim(str((convert (dec (15,2),sf.dbsize) + convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as [database_size],
UnallocatedSpace = CAST((CASE WHEN sf.dbsize >= pages.reservedpages THEN (sf.dbsize - pages.reservedpages) / 128.0 ELSE 0 END) AS numeric(20, 2)),
--ltrim(str((case when sf.dbsize >= pages.reservedpages then
-- (convert (dec (15,2),sf.dbsize) - convert (dec (15,2),pages.reservedpages))
-- * 8192 / 1048576 else 0 end),15,2) + ' MB') as [unallocated space],
-- from second result set of 'exec sp_spacedused'
ReservedKB = pages.reservedpages * 8,
--ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) + ' KB') as [reserved],
DataKB = pages.pages * 8,
--ltrim(str(pages.pages * 8192 / 1024.,15,0) + ' KB') as data,
IndexSizeKB = (pages.usedpages - pages.pages) * 8,
--ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) + ' KB') as index_size,
UnusedKB = (pages.reservedpages - pages.usedpages) * 8,
--ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) + ' KB') as unused,
-- additional:
ltrim(str((convert (dec (15,2),sf.dbsize)) * 8192 / 1048576,15,2) + ' MB') as dbsize,
ltrim(str((convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as logsize
FROM
(
select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) as dbsize,
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) as logsize
from dbo.sysfiles (nolock)
) sf,
(
select
sum(a.total_pages) as reservedpages,
sum(a.used_pages) as usedpages,
sum(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) as pages
from sys.partitions p (nolock)
join sys.allocation_units a (nolock) on p.partition_id = a.container_id
left join sys.internal_tables it (nolock) on p.object_id = it.object_id
) pages
--*/
--exec sp_spaceused
uspEmailDatabaseLogStatus: Monitoring Log files
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspEmailDatabaseLogStatus') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspEmailDatabaseLogStatus
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: May 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailDatabaseLogStatus
as
begin
set nocount on;
declare
@body varchar(max),
@text varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@RedAlertPercent int = 5,
@YellowAlertPercent int = 10,
@EmailCode dbo.EmailCode = 'LogFile_Report',
@Date date = GETDATE(),
@Subject varchar(max)
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end;
set @Subject = 'Log file Report for ' + convert(varchar(10), @Date, 120)
set @text =
CAST( (
select
td = left(dovs.volume_mount_point, 1), '',
td = mf.name, '',
td = mf.physical_name, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(cast(mf.size * 8 / 1024.0 as numeric(36, 0)) AS MONEY), 1), '.00' ,''), ''
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
where left(dovs.volume_mount_point, 1) in ('H', 'O')
and name like '%log'
order by mf.size desc, mf.name
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays Log file size on Phone Channel Data Warehouse Server (i.e.: CRSDPSSCA1IDCS\PC):
'
+ ''
+ '' +
+ 'Drive Name Physical name File Size
(GB)
+ replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
--print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
--------------------------------------------------------------------------------
GO
--exec uspEmailDatabaseLogStatus
/*
declare @date date = '2018-02-05'
exec uspEmailStorageStatus @date
*/
--select * from dbo.CtsJob
DROP PROCEDURE dbo.uspEmailDatabaseLogStatus
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: May 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailDatabaseLogStatus
as
begin
set nocount on;
declare
@body varchar(max),
@text varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@RedAlertPercent int = 5,
@YellowAlertPercent int = 10,
@EmailCode dbo.EmailCode = 'LogFile_Report',
@Date date = GETDATE(),
@Subject varchar(max)
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end;
set @Subject = 'Log file Report for ' + convert(varchar(10), @Date, 120)
set @text =
CAST( (
select
td = left(dovs.volume_mount_point, 1), '',
td = mf.name, '',
td = mf.physical_name, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(cast(mf.size * 8 / 1024.0 as numeric(36, 0)) AS MONEY), 1), '.00' ,''), ''
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
where left(dovs.volume_mount_point, 1) in ('H', 'O')
and name like '%log'
order by mf.size desc, mf.name
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays Log file size on Phone Channel Data Warehouse Server (i.e.: CRSDPSSCA1IDCS\PC):
'
+ '
+ '' +
+ '
(GB)
+ replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
--print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
--------------------------------------------------------------------------------
GO
--exec uspEmailDatabaseLogStatus
/*
declare @date date = '2018-02-05'
exec uspEmailStorageStatus @date
*/
--select * from dbo.CtsJob
uspEmailDriveStatus: Monitoring disk only
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspEmailDriveStatus') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspEmailDriveStatus
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: May 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailDriveStatus
as
begin
set nocount on;
declare
@body varchar(max),
@text varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@RedAlertPercent int = 5,
@YellowAlertPercent int = 10,
@EmailCode dbo.EmailCode = 'Drive_Report',
@Date date = GETDATE(),
@Subject varchar(max)
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end;
create table #disk (
Drive varchar(10),
MBFree FLOAT,
MBTotal FLOAT,
DriveName varchar(100))
INSERT INTO #disk(Drive, MBFree)
EXEC MASTER..xp_fixeddrives
update #disk set
DriveName = t.LogicalName,
MBFree = t.FreeSpaceInMB,
MBTotal = t.TotalSpaceInMB
from #disk di,
(
SELECT DISTINCT
dovs.logical_volume_name AS LogicalName,
left(dovs.volume_mount_point, 1) AS Drive,
cast(dovs.available_bytes / 1048576.0 as numeric(36, 2)) AS FreeSpaceInMB,
cast(dovs.total_bytes / 1048576.0 as numeric(36, 2)) AS TotalSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
) t
where di.Drive = t.Drive
declare @diskInfo table (
Drive varchar(10),
DriveName varchar(100),
FreeSpaceInGB FLOAT,
TotalSpaceInGB FLOAT,
PercentLeft float,
Critical varchar(10))
insert into @diskInfo (Drive, DriveName, FreeSpaceInGB, TotalSpaceInGB, PercentLeft, Critical)
select
t.Drive,
t.DriveName,
t.FreeSpaceInGB,
t.TotalSpaceInGB,
t.PercentLeft,
Critical = case when t.PercentLeft < 10 then '***' else '' end
from
(
select
Drive,
DriveName = ISNULL(DriveName, ''),
cast(MBFree / 1024 as numeric(36, 2)) AS FreeSpaceInGB,
cast(MBTotal / 1024.0 as numeric(36, 0)) AS TotalSpaceInGB,
cast(MBFree * 100.0 / MBTotal as numeric(36, 3)) AS PercentLeft
from #disk
WHERE MBTotal IS not null
) t
drop table #disk
set @Subject = 'Server Disk Report for ' + convert(varchar(10), @Date, 120)
--select
-- [@bgcolor] = CASE when PercentLeft < 5 then '#FF0000' --red/#E41B17
-- when PercentLeft < 10 then '#FFFF00' --yellow/#EAC117
-- ELSE '#00FF00' --green/#4AA02C
-- END,
-- td = Drive, '',
-- td = DriveName, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(TotalSpaceInGB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(FreeSpaceInGB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = CAST(CAST(PercentLeft AS numeric(36, 1)) AS VARCHAR) + '%', '',
-- [td/@align] = 'center',
-- td = Critical
--from @diskInfo
--order by CASE when PercentLeft < 5 then 1
-- when PercentLeft < 10 then 2
-- ELSE 3
-- END, Drive
set @text =
CAST( (
select
[@bgcolor] = CASE when PercentLeft < 10 then '#FF0000' --red/#E41B17
when PercentLeft < 15 then '#FFFF00' --yellow/#EAC117
ELSE '#00FF00' --green/#4AA02C
END,
color = CASE when PercentLeft < 5 then 'white' else 'black' END,
td = Drive, '',
td = DriveName, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(TotalSpaceInGB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(FreeSpaceInGB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = CAST(CAST(PercentLeft AS numeric(36, 1)) AS VARCHAR) + '%', '',
[td/@align] = 'center',
td = Critical
from @diskInfo
order by CASE when PercentLeft < 10 then 1
when PercentLeft < 15 then 2
ELSE 3
END, Drive
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays space usage on Phone Channel Data Warehouse Server (i.e.: CRSDPSSCA1IDCS\PC):
'
+ ''
+ 'white < 10%
' + 'black < 15%
' + ' OK
' + '
' + ''
+ '' +
+ 'Drive Drive Name Total Space
(GB) Free Space
(GB) Free Space
(%) Critical
(MB)
+ replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
set @body = REPLACE(@body, 'white ', '')
set @body = REPLACE(@body, 'black ', '')
set @body = REPLACE(@body, 'close ', '
')
--print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
--------------------------------------------------------------------------------
GO
--exec uspEmailDriveStatus
/*
declare @date date = '2018-02-05'
exec uspEmailStorageStatus @date
*/
--select * from dbo.CtsJob
DROP PROCEDURE dbo.uspEmailDriveStatus
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: May 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailDriveStatus
as
begin
set nocount on;
declare
@body varchar(max),
@text varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@RedAlertPercent int = 5,
@YellowAlertPercent int = 10,
@EmailCode dbo.EmailCode = 'Drive_Report',
@Date date = GETDATE(),
@Subject varchar(max)
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end;
create table #disk (
Drive varchar(10),
MBFree FLOAT,
MBTotal FLOAT,
DriveName varchar(100))
INSERT INTO #disk(Drive, MBFree)
EXEC MASTER..xp_fixeddrives
update #disk set
DriveName = t.LogicalName,
MBFree = t.FreeSpaceInMB,
MBTotal = t.TotalSpaceInMB
from #disk di,
(
SELECT DISTINCT
dovs.logical_volume_name AS LogicalName,
left(dovs.volume_mount_point, 1) AS Drive,
cast(dovs.available_bytes / 1048576.0 as numeric(36, 2)) AS FreeSpaceInMB,
cast(dovs.total_bytes / 1048576.0 as numeric(36, 2)) AS TotalSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
) t
where di.Drive = t.Drive
declare @diskInfo table (
Drive varchar(10),
DriveName varchar(100),
FreeSpaceInGB FLOAT,
TotalSpaceInGB FLOAT,
PercentLeft float,
Critical varchar(10))
insert into @diskInfo (Drive, DriveName, FreeSpaceInGB, TotalSpaceInGB, PercentLeft, Critical)
select
t.Drive,
t.DriveName,
t.FreeSpaceInGB,
t.TotalSpaceInGB,
t.PercentLeft,
Critical = case when t.PercentLeft < 10 then '***' else '' end
from
(
select
Drive,
DriveName = ISNULL(DriveName, ''),
cast(MBFree / 1024 as numeric(36, 2)) AS FreeSpaceInGB,
cast(MBTotal / 1024.0 as numeric(36, 0)) AS TotalSpaceInGB,
cast(MBFree * 100.0 / MBTotal as numeric(36, 3)) AS PercentLeft
from #disk
WHERE MBTotal IS not null
) t
drop table #disk
set @Subject = 'Server Disk Report for ' + convert(varchar(10), @Date, 120)
--select
-- [@bgcolor] = CASE when PercentLeft < 5 then '#FF0000' --red/#E41B17
-- when PercentLeft < 10 then '#FFFF00' --yellow/#EAC117
-- ELSE '#00FF00' --green/#4AA02C
-- END,
-- td = Drive, '',
-- td = DriveName, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(TotalSpaceInGB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(FreeSpaceInGB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = CAST(CAST(PercentLeft AS numeric(36, 1)) AS VARCHAR) + '%', '',
-- [td/@align] = 'center',
-- td = Critical
--from @diskInfo
--order by CASE when PercentLeft < 5 then 1
-- when PercentLeft < 10 then 2
-- ELSE 3
-- END, Drive
set @text =
CAST( (
select
[@bgcolor] = CASE when PercentLeft < 10 then '#FF0000' --red/#E41B17
when PercentLeft < 15 then '#FFFF00' --yellow/#EAC117
ELSE '#00FF00' --green/#4AA02C
END,
color = CASE when PercentLeft < 5 then 'white' else 'black' END,
td = Drive, '',
td = DriveName, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(TotalSpaceInGB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(FreeSpaceInGB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = CAST(CAST(PercentLeft AS numeric(36, 1)) AS VARCHAR) + '%', '',
[td/@align] = 'center',
td = Critical
from @diskInfo
order by CASE when PercentLeft < 10 then 1
when PercentLeft < 15 then 2
ELSE 3
END, Drive
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays space usage on Phone Channel Data Warehouse Server (i.e.: CRSDPSSCA1IDCS\PC):
'
+ '
+ '
' + '
' + '
' + '
' + '
+ '' +
+ '
(GB)
(GB)
(%)
(MB)
+ replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
')
--print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
--------------------------------------------------------------------------------
GO
--exec uspEmailDriveStatus
/*
declare @date date = '2018-02-05'
exec uspEmailStorageStatus @date
*/
--select * from dbo.CtsJob
uspLoadJobHistory
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspLoadJobHistory') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspLoadJobHistory
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspLoadJobHistory(@Date date)
as
begin
set nocount on;
declare
@result int = 0,
@CtsJobHistoryHeaderId dbo.CtsJobHistoryHeaderId
if exists (select * from dbo.CtsJobHistoryHeader where RunDate = @Date)
begin
return @result
end
MERGE dbo.CtsJob AS tgt
USING (
select
job_id, name, [enabled],
IsSchedule = CASE WHEN [enabled] = 1 AND
EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules js, msdb.dbo.sysschedules s
WHERE js.schedule_id = s.schedule_id and js.job_id = j.job_id and s.[enabled] = 1)
THEN 1
ELSE 0
END
from msdb.dbo.sysjobs j
) AS src
ON tgt.JobId = src.job_id
WHEN MATCHED THEN
UPDATE SET JobName = src.name, IsActive = [enabled]
WHEN NOT MATCHED BY TARGET THEN
INSERT (JobId, JobName, IsActive, ShowInReport) VALUES (job_id, name, [enabled], IsSchedule);
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.CtsJobHistoryHeader(RunDate) values (@Date)
SELECT @CtsJobHistoryHeaderId = SCOPE_IDENTITY();
WITH cteJob(job_id, run_date, run_time) as
(
select
job_id,
run_date = min(run_date),
run_time = max(run_time)
from msdb.dbo.sysjobhistory
where CAST(CAST(run_date as varchar) AS DATE) = @Date
and step_id = 0
group by job_id
), scheduleJob (job_id) as
(
select distinct
j.job_id
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 dbo.fnIsSchduledForDate(@Date, s.[enabled], s.freq_type, s.freq_interval, s.freq_relative_interval, s.active_start_date, s.active_end_date) = 1
)
INSERT INTO dbo.CtsJobHistoryDetail(CtsJobHistoryHeaderId, CtsJobId, RunTime, RunDuration, RunStatus, IsScheduled)
SELECT
@CtsJobHistoryHeaderId,
CtsJob.CtsJobId,
Run_time = CAST(DATEADD(ss, hist.run_time % 100, DATEADD(mi, (hist.run_time / 100) % 100, DATEADD(hh, hist.run_time / 10000, '00:00:00'))) AS TIME),
RunDuration = (hist.run_duration / 10000) * 3600 + ((hist.run_duration / 100) % 100) * 60 + hist.run_duration % 100,
hist.run_status,
CASE WHEN sj.job_id IS NOT NULL THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobhistory hist
JOIN cteJob j ON hist.job_id = j.job_id and hist.step_id = 0 and hist.run_date = j.run_date and hist.run_time = j.run_time
JOIN dbo.CtsJob ON hist.job_id = CtsJob.JobId
LEFT JOIN scheduleJob sj ON hist.job_id = sj.job_id
COMMIT TRANSACTION
END TRY
BEGIN CATCH
declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
select
@ErrorMessage = ERROR_MESSAGE(), --+ ' Line ' + cast(ERROR_LINE() as nvarchar(5)),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @@TRANCOUNT > 0
begin
rollback transaction
end
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
set @result = 1
END CATCH
return @result
end
--------------------------------------------------------------------------------
GO
/*
declare @date date = '2018-01-01'
while @date < dateadd(dd, -1, getdate())
begin
exec uspLoadJobHistory @date
set @date = dateadd(dd, 1, @date)
end
*/
--select * from dbo.CtsJob
DROP PROCEDURE dbo.uspLoadJobHistory
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspLoadJobHistory(@Date date)
as
begin
set nocount on;
declare
@result int = 0,
@CtsJobHistoryHeaderId dbo.CtsJobHistoryHeaderId
if exists (select * from dbo.CtsJobHistoryHeader where RunDate = @Date)
begin
return @result
end
MERGE dbo.CtsJob AS tgt
USING (
select
job_id, name, [enabled],
IsSchedule = CASE WHEN [enabled] = 1 AND
EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules js, msdb.dbo.sysschedules s
WHERE js.schedule_id = s.schedule_id and js.job_id = j.job_id and s.[enabled] = 1)
THEN 1
ELSE 0
END
from msdb.dbo.sysjobs j
) AS src
ON tgt.JobId = src.job_id
WHEN MATCHED THEN
UPDATE SET JobName = src.name, IsActive = [enabled]
WHEN NOT MATCHED BY TARGET THEN
INSERT (JobId, JobName, IsActive, ShowInReport) VALUES (job_id, name, [enabled], IsSchedule);
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.CtsJobHistoryHeader(RunDate) values (@Date)
SELECT @CtsJobHistoryHeaderId = SCOPE_IDENTITY();
WITH cteJob(job_id, run_date, run_time) as
(
select
job_id,
run_date = min(run_date),
run_time = max(run_time)
from msdb.dbo.sysjobhistory
where CAST(CAST(run_date as varchar) AS DATE) = @Date
and step_id = 0
group by job_id
), scheduleJob (job_id) as
(
select distinct
j.job_id
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 dbo.fnIsSchduledForDate(@Date, s.[enabled], s.freq_type, s.freq_interval, s.freq_relative_interval, s.active_start_date, s.active_end_date) = 1
)
INSERT INTO dbo.CtsJobHistoryDetail(CtsJobHistoryHeaderId, CtsJobId, RunTime, RunDuration, RunStatus, IsScheduled)
SELECT
@CtsJobHistoryHeaderId,
CtsJob.CtsJobId,
Run_time = CAST(DATEADD(ss, hist.run_time % 100, DATEADD(mi, (hist.run_time / 100) % 100, DATEADD(hh, hist.run_time / 10000, '00:00:00'))) AS TIME),
RunDuration = (hist.run_duration / 10000) * 3600 + ((hist.run_duration / 100) % 100) * 60 + hist.run_duration % 100,
hist.run_status,
CASE WHEN sj.job_id IS NOT NULL THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobhistory hist
JOIN cteJob j ON hist.job_id = j.job_id and hist.step_id = 0 and hist.run_date = j.run_date and hist.run_time = j.run_time
JOIN dbo.CtsJob ON hist.job_id = CtsJob.JobId
LEFT JOIN scheduleJob sj ON hist.job_id = sj.job_id
COMMIT TRANSACTION
END TRY
BEGIN CATCH
declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
select
@ErrorMessage = ERROR_MESSAGE(), --+ ' Line ' + cast(ERROR_LINE() as nvarchar(5)),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @@TRANCOUNT > 0
begin
rollback transaction
end
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
set @result = 1
END CATCH
return @result
end
--------------------------------------------------------------------------------
GO
/*
declare @date date = '2018-01-01'
while @date < dateadd(dd, -1, getdate())
begin
exec uspLoadJobHistory @date
set @date = dateadd(dd, 1, @date)
end
*/
--select * from dbo.CtsJob
uspEmailJobHistoryRecent
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspEmailJobHistoryRecent') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspEmailJobHistoryRecent
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailJobHistoryRecent
as
begin
set nocount on;
declare
@date datetime,
@body varchar(max),
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@EmailCode dbo.EmailCode = 'Job_Report_Recent',
@Subject varchar(max) = 'Job History'
select @Date = GETDATE()
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end
declare @tJobHist table(
JobName sysname,
RunDate date,
Runtime time,
RunDateTime datetime,
MinutesElapsed int,
RunDuration RunDuration,
RunStatus RunStatus,
ReportStatus int
);
with cteJob(job_id, name, [enabled], IsSchedule) as
(
select
job_id, name, [enabled],
IsSchedule = CASE WHEN [enabled] = 1 AND
EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules js, msdb.dbo.sysschedules s
WHERE js.schedule_id = s.schedule_id and js.job_id = j.job_id and s.[enabled] = 1)
THEN 1
ELSE 0
END
from msdb.dbo.sysjobs j
), scheduledJob (job_id) as
(
select distinct
j.job_id
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 dbo.fnIsSchduledForDate(DATEADD(dd, -1, @Date), s.[enabled], s.freq_type, s.freq_interval, s.freq_relative_interval, s.active_start_date, s.active_end_date) = 1
), cteReport(job_id, name, run_date, run_time, RunDuration, run_status) as
(
SELECT
j.job_id,
j.name,
Run_Date = CAST(CAST(hist.run_date as varchar) AS DATETIME),
Run_time = CAST(DATEADD(ss, hist.run_time % 100, DATEADD(mi, (hist.run_time / 100) % 100, DATEADD(hh, hist.run_time / 10000, '00:00:00'))) AS TIME),
RunDuration = (hist.run_duration / 10000) * 3600 + ((hist.run_duration / 100) % 100) * 60 + hist.run_duration % 100,
hist.run_status
FROM cteJob j
OUTER APPLY
(
select top 1
hist.run_date,
hist.run_time,
hist.run_duration,
hist.run_status
from msdb.dbo.sysjobhistory hist
where hist.job_id = j.job_id and hist.step_id = 0
and hist.run_status in (0, 1, 3) --0: Failed, 1: Succeeded, 3:Cancelled
order by hist.run_date desc, hist.run_time desc
) hist
where j.IsSchedule = 1
)
INSERT INTO @tJobHist(JobName, RunDate, Runtime, RunDateTime, MinutesElapsed, RunDuration, RunStatus, ReportStatus)
select
r.name,
r.run_date,
r.run_time,
RunDateTime = r.run_date + r.run_time,
MinutesElapsed = DATEDIFF(minute, r.run_date + r.run_time, @date),
r.RunDuration,
r.run_status,
ReportStatus = CASE when r.run_status = 0 then 0 --Failed
when r.run_status = 3 then 1 --Cancelled
--when sj.job_id IS NOT NULL AND ISNULL(DATEDIFF(minute, r.run_date + r.run_time, @date), 1441) > 24 * 60 then 2 --Scheduled but not run in last 24 hours
when sj.job_id IS NOT NULL AND DATEDIFF(day, r.run_date, @date) > 1 then 2 --Scheduled but not run in last 24 hours
when r.run_status = 1 then 3 --Succeeded
END
from cteReport r
left join scheduledJob sj on r.job_id = sj.job_id
--select
-- [@bgcolor] = CASE ReportStatus WHEN 0 THEN '#FF0000' --red/#E41B17
-- WHEN 1 THEN '#FFFF00' --yellow/#EAC117
-- WHEN 2 THEN '#800080' --coffee/#808080
-- WHEN 3 then '#00FF00' --green/#4AA02C
-- ELSE '#7D0552' --Plum Velvet/#7D0552
-- END,
-- ReportStatus,
-- color = CASE when ReportStatus in (0, 1, 2) then 'black' else 'white' END,
-- JobName,
-- RunDate = CAST(RunDate AS varchar(10)),
-- RunTime = CAST(RunTime AS varchar(8)),
-- Duration = case when RunDuration > 3600 then cast(RunDuration / 3600 as varchar) + ':' else '' end
-- + right('0' + cast((RunDuration % 3600) / 60 as varchar), 2) + ':'
-- + right('0' + cast(RunDuration % 60 as varchar), 2),
-- [status] = case RunStatus when 0 then 'Failed'
-- when 1 then 'Succeeded'
-- when 2 then 'Retry'
-- when 3 then 'Cancelled'
-- end
--from @tJobHist
--order by ReportStatus, JobName
set @Subject = 'SQL Agent Job Status Report'-- for ' + convert(varchar(10), @Date, 120)
print @subject
set @body =
CAST( (
select
[@bgcolor] = CASE ReportStatus WHEN 0 THEN '#FF0000' --red/#E41B17
WHEN 1 THEN '#FFFF00' --yellow/#EAC117
WHEN 2 THEN '#800080' --coffee/#808080
WHEN 3 then '#2ECC40' --green/#00FF00
ELSE '#7D0552' --Plum Velvet/#7D0552
END,
color = CASE when ReportStatus in (0, 1, 2) then 'white' else 'black' END,
td = JobName, '',
td = CAST(RunDate AS varchar(10)), '',
td = CAST(RunTime AS varchar(8)), '',
[td/@align] = 'right',
td = case when RunDuration > 3600 then cast(RunDuration / 3600 as varchar) + ':' else '' end
+ right('0' + cast((RunDuration % 3600) / 60 as varchar), 2) + ':'
+ right('0' + cast(RunDuration % 60 as varchar), 2), '',
td = case RunStatus when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
end, '',
color = 'close'
from @tJobHist
order by ReportStatus, JobName
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays the "most recent" (latest) job status of the SQL scheduled jobs on Phone Channel Data Warehouse (i.e.: CRSDPSSCA1IDCS\PC)
'
+ ''
+ 'white Failed
' + 'black Cancelled
' + 'white Not ran since yesterday
' + ' Succeeded
' + '
' + ''
+ '' +
+ 'Job Name Run Date Start Time Duration Status
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
' --00FFFF-->DDDDDD-->39CCCC
set @body = REPLACE(@body, 'white ', '')
set @body = REPLACE(@body, 'black ', '')
set @body = REPLACE(@body, 'close ', '
')
-- print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
GO
--exec uspEmailJobHistoryRecent
/*
declare @date date = '2018-02-05'
exec uspEmailJobHistory @date
*/
--select * from dbo.CtsJob
DROP PROCEDURE dbo.uspEmailJobHistoryRecent
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailJobHistoryRecent
as
begin
set nocount on;
declare
@date datetime,
@body varchar(max),
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@EmailCode dbo.EmailCode = 'Job_Report_Recent',
@Subject varchar(max) = 'Job History'
select @Date = GETDATE()
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end
declare @tJobHist table(
JobName sysname,
RunDate date,
Runtime time,
RunDateTime datetime,
MinutesElapsed int,
RunDuration RunDuration,
RunStatus RunStatus,
ReportStatus int
);
with cteJob(job_id, name, [enabled], IsSchedule) as
(
select
job_id, name, [enabled],
IsSchedule = CASE WHEN [enabled] = 1 AND
EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules js, msdb.dbo.sysschedules s
WHERE js.schedule_id = s.schedule_id and js.job_id = j.job_id and s.[enabled] = 1)
THEN 1
ELSE 0
END
from msdb.dbo.sysjobs j
), scheduledJob (job_id) as
(
select distinct
j.job_id
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 dbo.fnIsSchduledForDate(DATEADD(dd, -1, @Date), s.[enabled], s.freq_type, s.freq_interval, s.freq_relative_interval, s.active_start_date, s.active_end_date) = 1
), cteReport(job_id, name, run_date, run_time, RunDuration, run_status) as
(
SELECT
j.job_id,
j.name,
Run_Date = CAST(CAST(hist.run_date as varchar) AS DATETIME),
Run_time = CAST(DATEADD(ss, hist.run_time % 100, DATEADD(mi, (hist.run_time / 100) % 100, DATEADD(hh, hist.run_time / 10000, '00:00:00'))) AS TIME),
RunDuration = (hist.run_duration / 10000) * 3600 + ((hist.run_duration / 100) % 100) * 60 + hist.run_duration % 100,
hist.run_status
FROM cteJob j
OUTER APPLY
(
select top 1
hist.run_date,
hist.run_time,
hist.run_duration,
hist.run_status
from msdb.dbo.sysjobhistory hist
where hist.job_id = j.job_id and hist.step_id = 0
and hist.run_status in (0, 1, 3) --0: Failed, 1: Succeeded, 3:Cancelled
order by hist.run_date desc, hist.run_time desc
) hist
where j.IsSchedule = 1
)
INSERT INTO @tJobHist(JobName, RunDate, Runtime, RunDateTime, MinutesElapsed, RunDuration, RunStatus, ReportStatus)
select
r.name,
r.run_date,
r.run_time,
RunDateTime = r.run_date + r.run_time,
MinutesElapsed = DATEDIFF(minute, r.run_date + r.run_time, @date),
r.RunDuration,
r.run_status,
ReportStatus = CASE when r.run_status = 0 then 0 --Failed
when r.run_status = 3 then 1 --Cancelled
--when sj.job_id IS NOT NULL AND ISNULL(DATEDIFF(minute, r.run_date + r.run_time, @date), 1441) > 24 * 60 then 2 --Scheduled but not run in last 24 hours
when sj.job_id IS NOT NULL AND DATEDIFF(day, r.run_date, @date) > 1 then 2 --Scheduled but not run in last 24 hours
when r.run_status = 1 then 3 --Succeeded
END
from cteReport r
left join scheduledJob sj on r.job_id = sj.job_id
--select
-- [@bgcolor] = CASE ReportStatus WHEN 0 THEN '#FF0000' --red/#E41B17
-- WHEN 1 THEN '#FFFF00' --yellow/#EAC117
-- WHEN 2 THEN '#800080' --coffee/#808080
-- WHEN 3 then '#00FF00' --green/#4AA02C
-- ELSE '#7D0552' --Plum Velvet/#7D0552
-- END,
-- ReportStatus,
-- color = CASE when ReportStatus in (0, 1, 2) then 'black' else 'white' END,
-- JobName,
-- RunDate = CAST(RunDate AS varchar(10)),
-- RunTime = CAST(RunTime AS varchar(8)),
-- Duration = case when RunDuration > 3600 then cast(RunDuration / 3600 as varchar) + ':' else '' end
-- + right('0' + cast((RunDuration % 3600) / 60 as varchar), 2) + ':'
-- + right('0' + cast(RunDuration % 60 as varchar), 2),
-- [status] = case RunStatus when 0 then 'Failed'
-- when 1 then 'Succeeded'
-- when 2 then 'Retry'
-- when 3 then 'Cancelled'
-- end
--from @tJobHist
--order by ReportStatus, JobName
set @Subject = 'SQL Agent Job Status Report'-- for ' + convert(varchar(10), @Date, 120)
print @subject
set @body =
CAST( (
select
[@bgcolor] = CASE ReportStatus WHEN 0 THEN '#FF0000' --red/#E41B17
WHEN 1 THEN '#FFFF00' --yellow/#EAC117
WHEN 2 THEN '#800080' --coffee/#808080
WHEN 3 then '#2ECC40' --green/#00FF00
ELSE '#7D0552' --Plum Velvet/#7D0552
END,
color = CASE when ReportStatus in (0, 1, 2) then 'white' else 'black' END,
td = JobName, '',
td = CAST(RunDate AS varchar(10)), '',
td = CAST(RunTime AS varchar(8)), '',
[td/@align] = 'right',
td = case when RunDuration > 3600 then cast(RunDuration / 3600 as varchar) + ':' else '' end
+ right('0' + cast((RunDuration % 3600) / 60 as varchar), 2) + ':'
+ right('0' + cast(RunDuration % 60 as varchar), 2), '',
td = case RunStatus when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
end, '',
color = 'close'
from @tJobHist
order by ReportStatus, JobName
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays the "most recent" (latest) job status of the SQL scheduled jobs on Phone Channel Data Warehouse (i.e.: CRSDPSSCA1IDCS\PC)
'
+ '
+ '
' + '
' + '
' + '
' + '
' + '
+ '' +
+ '
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
' --00FFFF-->DDDDDD-->39CCCC
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
')
-- print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
GO
--exec uspEmailJobHistoryRecent
/*
declare @date date = '2018-02-05'
exec uspEmailJobHistory @date
*/
--select * from dbo.CtsJob
uspEmailJobHistory
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspEmailJobHistory') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspEmailJobHistory
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailJobHistory(@Date date)
as
begin
set nocount on;
declare
@body varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@EmailCode dbo.EmailCode = 'Job_Report',
@Subject varchar(max) = 'Job History'
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end
declare @tJobHist table(
JobName sysname,
Runtime time,
RunDuration RunDuration,
RunStatus RunStatus
)
insert into @tJobHist (JobName, RunTime, RunDuration, RunStatus)
select
j.JobName,
hist.RunTime,
hist.RunDuration,
hist.RunStatus
from dbo.CtsJob j
left join
(
select
dtl.CtsJobId,
dtl.RunTime,
dtl.RunDuration,
dtl.RunStatus,
dtl.IsScheduled
from dbo.CtsJobHistoryHeader hdr
join dbo.CtsJobHistoryDetail dtl on hdr.CtsJobHistoryHeaderId = dtl.CtsJobHistoryHeaderId
where hdr.RunDate = @Date
and dtl.IsScheduled = 1
) hist on j.CtsJobId = hist.CtsJobId
where j.ShowInReport = 1
and hist.IsScheduled = 1
select @FailedCount = COUNT(*) from @tJobHist where RunStatus = 0
select @SuccessCount = COUNT(*) from @tJobHist where RunStatus = 1
select @NotRunCount = COUNT(*) from @tJobHist where RunStatus IS NULL
set @Subject = 'SQL Agent Job Status Report for ' + convert(varchar(10), @Date, 120)
-- + ' (Succeeded=' + cast(@SuccessCount as varchar)
-- + ', Failed=' + cast(@FailedCount as varchar)
-- + ', Not Run=' + cast(@NotRunCount as varchar) + ')'
print @subject
set @body =
CAST( (
select
[@bgcolor] = CASE RunStatus when 0 then '#FF0000' --red/#E41B17
when 1 then '#00FF00' --green/#4AA02C
when 2 then '#800080' --coffee/#808080
when 3 then '#FFFF00' --yellow/#EAC117
else '#7D0552' --Plum Velvet/#7D0552
END,
color = CASE when RunStatus in (1, 3) then 'black' else 'white' END,
td = JobName, '',
td = CAST(RunTime AS varchar(8)), '',
[td/@align] = 'right',
td = case when RunDuration > 3600 then cast(RunDuration / 3600 as varchar) + ':' else '' end
+ right('0' + cast((RunDuration % 3600) / 60 as varchar), 2) + ':'
+ right('0' + cast(RunDuration % 60 as varchar), 2), '',
td = case RunStatus when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
end, '',
color = CASE when RunStatus in (1, 3) then 'close' else 'close' END
from @tJobHist
order by case RunStatus when 0 then 0
when 1 then 3
when 2 then 1
when 3 then 2
end, JobName
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays the "most recent" (latest) job status of the SQL scheduled jobs on Phone Channel Data Warehouse (i.e.: CRSDPSSCA1IDCS\PC)
'
+ ''
+ '' +
+ 'Job Name Start Time Duration Status
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
'
set @body = REPLACE(@body, 'white ', '')
set @body = REPLACE(@body, 'black ', '')
set @body = REPLACE(@body, 'close ', '
')
--print @body
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @EmailProfile,
@body = @body,
@body_format ='HTML',
@recipients = @EmailTo,
@copy_recipients = @EmailCc,
@subject = @Subject;
insert into dbo.EmailLog(EmailCode, EmailProfile, EmailFrom, EmailTo, EmailCc, EmailSubject, EmailBody)
values (@EmailCode, @EmailProfile, @EmailFrom, @EmailTo, @EmailCc, @Subject, @body)
end
--------------------------------------------------------------------------------
GO
--exec uspEmailJobHistory '2018-02-05'
/*
declare @date date = '2018-02-05'
exec uspEmailJobHistory @date
*/
--select * from dbo.CtsJob
DROP PROCEDURE dbo.uspEmailJobHistory
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailJobHistory(@Date date)
as
begin
set nocount on;
declare
@body varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@EmailCode dbo.EmailCode = 'Job_Report',
@Subject varchar(max) = 'Job History'
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end
declare @tJobHist table(
JobName sysname,
Runtime time,
RunDuration RunDuration,
RunStatus RunStatus
)
insert into @tJobHist (JobName, RunTime, RunDuration, RunStatus)
select
j.JobName,
hist.RunTime,
hist.RunDuration,
hist.RunStatus
from dbo.CtsJob j
left join
(
select
dtl.CtsJobId,
dtl.RunTime,
dtl.RunDuration,
dtl.RunStatus,
dtl.IsScheduled
from dbo.CtsJobHistoryHeader hdr
join dbo.CtsJobHistoryDetail dtl on hdr.CtsJobHistoryHeaderId = dtl.CtsJobHistoryHeaderId
where hdr.RunDate = @Date
and dtl.IsScheduled = 1
) hist on j.CtsJobId = hist.CtsJobId
where j.ShowInReport = 1
and hist.IsScheduled = 1
select @FailedCount = COUNT(*) from @tJobHist where RunStatus = 0
select @SuccessCount = COUNT(*) from @tJobHist where RunStatus = 1
select @NotRunCount = COUNT(*) from @tJobHist where RunStatus IS NULL
set @Subject = 'SQL Agent Job Status Report for ' + convert(varchar(10), @Date, 120)
-- + ' (Succeeded=' + cast(@SuccessCount as varchar)
-- + ', Failed=' + cast(@FailedCount as varchar)
-- + ', Not Run=' + cast(@NotRunCount as varchar) + ')'
print @subject
set @body =
CAST( (
select
[@bgcolor] = CASE RunStatus when 0 then '#FF0000' --red/#E41B17
when 1 then '#00FF00' --green/#4AA02C
when 2 then '#800080' --coffee/#808080
when 3 then '#FFFF00' --yellow/#EAC117
else '#7D0552' --Plum Velvet/#7D0552
END,
color = CASE when RunStatus in (1, 3) then 'black' else 'white' END,
td = JobName, '',
td = CAST(RunTime AS varchar(8)), '',
[td/@align] = 'right',
td = case when RunDuration > 3600 then cast(RunDuration / 3600 as varchar) + ':' else '' end
+ right('0' + cast((RunDuration % 3600) / 60 as varchar), 2) + ':'
+ right('0' + cast(RunDuration % 60 as varchar), 2), '',
td = case RunStatus when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
end, '',
color = CASE when RunStatus in (1, 3) then 'close' else 'close' END
from @tJobHist
order by case RunStatus when 0 then 0
when 1 then 3
when 2 then 1
when 3 then 2
end, JobName
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays the "most recent" (latest) job status of the SQL scheduled jobs on Phone Channel Data Warehouse (i.e.: CRSDPSSCA1IDCS\PC)
'
+ '
+ '' +
+ '
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
'
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
')
--print @body
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @EmailProfile,
@body = @body,
@body_format ='HTML',
@recipients = @EmailTo,
@copy_recipients = @EmailCc,
@subject = @Subject;
insert into dbo.EmailLog(EmailCode, EmailProfile, EmailFrom, EmailTo, EmailCc, EmailSubject, EmailBody)
values (@EmailCode, @EmailProfile, @EmailFrom, @EmailTo, @EmailCc, @Subject, @body)
end
--------------------------------------------------------------------------------
GO
--exec uspEmailJobHistory '2018-02-05'
/*
declare @date date = '2018-02-05'
exec uspEmailJobHistory @date
*/
--select * from dbo.CtsJob
uspEmailStorageStatus
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspEmailStorageStatus') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspEmailStorageStatus
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailStorageStatus(@Date date)
as
begin
set nocount on;
declare
@body varchar(max),
@text varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@RedAlertPercent int = 5,
@YellowAlertPercent int = 10,
@EmailCode dbo.EmailCode = 'Space_Report',
@Subject varchar(max) = 'Disk Space Report'
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end;
declare @tDb table(
DBName DBName,
Drive Drive,
DBSizeMB INT,
DriveTotalMB INT,
DriveFreeMB INT,
DailyDBUsedKB INT,
DailyDriveUsedKB INT,
DailyDBUsedKB30 INT,
DailyDriveUsedKB30 INT,
DaysLast int,
PercentAlert int --0: OK, 1: Yellow, 2: Red
);
with cte_Detail (DBName, Drive, DBSizeMB, UnallocatedSpaceMB, ReservedKB, DataKB, IndexSizeKB, UnusedKB, DriveTotalMB, DriveFreeMB,
LastRunDate, LastUsedKB, [Days], LastRunDate30, LastUsedKB30, Days30) as
(
select
db.DBName,
dr.Drive,
dbDtl.DBSizeMB,
dbDtl.UnallocatedSpaceMB,
dbDtl.ReservedKB,
dbDtl.DataKB,
dbDtl.IndexSizeKB,
dbDtl.UnusedKB,
dr.TotalSpaceInMB,
drDtl.FreeSpaceInMB,
hist.RunDate,
hist.UsedKB,
DATEDIFF(dd, hist.RunDate, @Date),
hist30.RunDate,
hist30.UsedKB,
DATEDIFF(dd, hist30.RunDate, @Date)
FROM dbo.StoHistoryHeader hdr
JOIN dbo.StoHistoryDbDetail dbDtl on hdr.StoHistoryHeaderId = dbDtl.StoHistoryHeaderId
JOIN dbo.StoDb db on dbDtl.StoDbId = db.StoDbId and db.IsActive = 1
LEFT JOIN dbo.StoDriveDbMap map on db.StoDbId = map.StoDbId and db.IsActive = 1
LEFT JOIN dbo.StoDrive dr on map.StoDriveId = dr.StoDriveId
LEFT JOIN dbo.StoHistoryDriveDetail drDtl on hdr.StoHistoryHeaderId = drDtl.StoHistoryHeaderId
and dr.StoDriveId = drDtl.StoDriveId
CROSS APPLY (select top 1
hdr2.RunDate,
UsedKB = dbDtl2.DataKB + dbDtl2.IndexSizeKB
from dbo.StoHistoryDbDetail dbDtl2
join dbo.StoHistoryHeader hdr2 on dbDtl2.StoHistoryHeaderId = hdr2.StoHistoryHeaderId
where dbDtl2.StoDbId = dbDtl.StoDbId
order by hdr2.RunDate
) hist
CROSS APPLY (select top 1
hdr2.RunDate,
UsedKB = dbDtl2.DataKB + dbDtl2.IndexSizeKB
from dbo.StoHistoryDbDetail dbDtl2
join dbo.StoHistoryHeader hdr2 on dbDtl2.StoHistoryHeaderId = hdr2.StoHistoryHeaderId
where dbDtl2.StoDbId = dbDtl.StoDbId
and hdr2.RunDate >= DATEADD(mm, -1, @Date)
order by hdr2.RunDate
) hist30
where CAST(hdr.RunDate AS DATE) = @Date
), cte_DB (DBName, Drive, DBSizeMB, UsedKB, LastDate, LastUsedKB, DriveTotalMB, DriveFreeMB, DailyDbUsedKB, DailyDbUsedKB30) as
(
select
cte.DBName,
cte.Drive,
cte.DBSizeMB,
CurrentSize = cte.DataKB + cte.IndexSizeKB,
cte.LastRunDate,
cte.LastUsedKB,
cte.DriveTotalMB,
cte.DriveFreeMB,
DailyDbUsedKB = CASE WHEN (cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB > 0
THEN ((cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB) / [Days]
ELSE 0 END,
DailyDbUsedKB30 = CASE WHEN (cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB30 > 0
THEN ((cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB30) / [Days30]
ELSE 0 END
from cte_Detail cte
), cte_Drive (Drive, DriveTotalMB, DriveFreeMB, DailyDriveUsedKB, DailyDriveUsedKB30, DaysLast) as
(
select
Drive,
MAX(DriveTotalMB),
MAX(DriveFreeMB),
SUM(DailyDbUsedKB),
SUM(DailyDbUsedKB30),
DaysLast = CASE WHEN SUM(DailyDbUsedKB30 * 30.0 / 1000.0) < 1.0 THEN NULL
ELSE MAX(DriveFreeMB) / SUM(DailyDbUsedKB30 / 1000.0) END
from cte_DB
group by Drive
)
insert into @tDb (DBName, Drive, DBSizeMB, DriveTotalMB, DriveFreeMB, DailyDBUsedKB, DailyDriveUsedKB, DailyDBUsedKB30, DailyDriveUsedKB30, DaysLast, PercentAlert)
select
db.DBName,
db.Drive,
db.DBSizeMB,
db.DriveTotalMB,
db.DriveFreeMB,
db.DailyDbUsedKB,
dr.DailyDriveUsedKB,
db.DailyDBUsedKB30,
dr.DailyDriveUsedKB30,
dr.DaysLast,
PercentAlert = case when dr.DriveTotalMB = 0 then 0
when dr.DriveFreeMB * 100.0 / dr.DriveTotalMB < @YellowAlertPercent then 1
when dr.DriveFreeMB * 100.0 / dr.DriveTotalMB < @RedAlertPercent then 2
else 0 end
from cte_DB db
join cte_Drive dr on db.Drive = dr.Drive
set @Subject = 'Server Space Report for ' + convert(varchar(10), @Date, 120)
--select
-- [@bgcolor] = CASE when DaysLast < 30 then '#FF0000' --red/#E41B17
-- when DaysLast < 90 then '#FFFF00' --yellow/#EAC117
-- when DaysLast >= 90 then '#00FF00' --green/#4AA02C
-- ELSE '#800080' --coffee/#4AA02C
-- END,
-- td = Drive, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DriveTotalMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DriveFreeMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- FreePercent = CAST(CAST(DriveFreeMB * 100.0 / DriveTotalMB AS numeric(36, 1)) AS VARCHAR) + '%', '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = dbo.fnDaysLast(DaysLast), ''
--from
--(
-- select
-- Drive,
-- DriveTotalMB = MAX(DriveTotalMB),
-- DriveFreeMB = MAX(DriveFreeMB),
-- DailyDriveUsedKB = MAX(DailyDriveUsedKB ),
-- DailyDriveUsedKB30 = MAX(DailyDriveUsedKB30),
-- DaysLast = MAX(DaysLast)
-- from @tDb
-- group by Drive
--) t
--order by CASE when DaysLast < 30 then 1
-- when DaysLast < 90 then 2
-- ELSE 3
-- END, Drive
set @text =
CAST( (
select
[@bgcolor] = CASE when DaysLast < 30 or PercentAlert = 2 then '#FF0000' --red/#E41B17
when DaysLast < 90 or PercentAlert = 1 then '#FFFF00' --yellow/#EAC117
when DaysLast >= 90 then '#00FF00' --green/#4AA02C
ELSE '#2ECC40' --coffee/#4AA02C/#800080
END,
color = CASE when DaysLast < 30 then 'white' else 'black' END,
td = Drive, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DriveTotalMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DriveFreeMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
FreePercent = CAST(CAST(DriveFreeMB * 100.0 / DriveTotalMB AS numeric(36, 1)) AS VARCHAR) + '%', '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = dbo.fnDaysLast(DaysLast), ''
from
(
select
Drive,
DriveTotalMB = MAX(DriveTotalMB),
DriveFreeMB = MAX(DriveFreeMB),
DailyDriveUsedKB = MAX(DailyDriveUsedKB ),
DailyDriveUsedKB30 = MAX(DailyDriveUsedKB30),
DaysLast = MAX(DaysLast),
PercentAlert = MAX(PercentAlert)
from @tDb
group by Drive
UNION ALL
select
StoDrive.Drive,
DriveTotalMB = CAST(StoDrive.TotalSpaceInMB AS INT),
DriveFreeMB = CAST(drDtl.FreeSpaceInMB AS INT),
DailyDriveUsedKB = NULL,
DailyDriveUsedKB30 = NULL,
DaysLast = NULL,
PercentAlert = case when StoDrive.TotalSpaceInMB = 0 then 0
when drDtl.FreeSpaceInMB * 100.0 / StoDrive.TotalSpaceInMB < @YellowAlertPercent then 1
when drDtl.FreeSpaceInMB * 100.0 / StoDrive.TotalSpaceInMB < @RedAlertPercent then 2
else 0 end
from dbo.StoDrive
JOIN dbo.StoHistoryHeader hdr on CAST(hdr.RunDate AS DATE) = @Date
JOIN dbo.StoHistoryDriveDetail drDtl on hdr.StoHistoryHeaderId = drDtl.StoHistoryHeaderId and StoDrive.StoDriveId = drDtl.StoDriveId
where StoDrive.Drive not in (select Drive from @tDb)
and StoDrive.IsActive = 1
) t
order by CASE when DaysLast < 30 or PercentAlert = 2 then 1
when DaysLast < 90 or PercentAlert = 1 then 2
ELSE 3
END, Drive
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays space usage on Phone Channel Data Warehouse Server (i.e.: CRSDPSSCA1IDCS\PC):
'
+ ''
+ 'white < 30 days or 5%
' + 'black < 90 days or 10%
' + 'black No usage
' + ' OK
' + '
' + ''
+ '' +
+ 'Drive Total Space
(MB) Free Space
(MB) Free Space
(%) Overall Monthly
(MB) Last Month
(MB) Estimated Days Last
+ replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
--select
-- [@bgcolor] = CASE when DaysLast < 30 then '#FF0000' --red/#E41B17
-- when DaysLast < 90 then '#FFFF00' --yellow/#EAC117
-- when DaysLast >= 90 then '#00FF00' --green/#4AA02C
-- ELSE '#800080' --green/#4AA02C
-- END,
-- td = DBName, '',
-- td = Drive, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DBSizeMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB * 30 / 1000.0 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB30 * 30 / 1000.0 AS MONEY), 1), '.00' ,''), '',
-- color = CASE when DaysLast >= 90 then 'close' else 'close' END
--from @tDb
--order by CASE when DaysLast < 30 then 1
-- when DaysLast < 90 then 2
-- ELSE 3
-- END, DBName
set @text =
CAST( (
select
[@bgcolor] = CASE when DaysLast < 30 or PercentAlert = 2 then '#FF0000' --red/#E41B17
when DaysLast < 90 or PercentAlert = 1 then '#FFFF00' --yellow/#EAC117
when DaysLast >= 90 then '#00FF00' --green/#4AA02C
ELSE '#2ECC40' --green/#4AA02C
END,
color = CASE when DaysLast < 30 then 'white' else 'black' END,
td = DBName, '',
td = Drive, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DBSizeMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
color = CASE when DaysLast >= 90 then 'close' else 'close' END
from @tDb
order by CASE when DaysLast < 30 or PercentAlert = 2 then 1
when DaysLast < 90 or PercentAlert = 1 then 2
ELSE 3
END, DBName
for xml path( 'tr' ), type
) as varchar(max) )
set @body = @body + '
The following summary displays disk usage for all databases:
'
+ ''
+ '' +
+ 'DB Name Drive DB Size
(MB) '
+ 'Overall Monthly
(MB) Last Month
(MB)
' + replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
set @body = REPLACE(@body, 'white ', '')
set @body = REPLACE(@body, 'black ', '')
set @body = REPLACE(@body, 'close ', '
')
--print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
--------------------------------------------------------------------------------
GO
--exec uspEmailStorageStatus '2018-04-03'
/*
declare @date date = '2018-02-05'
exec uspEmailStorageStatus @date
*/
--select * from dbo.CtsJob
DROP PROCEDURE dbo.uspEmailStorageStatus
GO
--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailStorageStatus(@Date date)
as
begin
set nocount on;
declare
@body varchar(max),
@text varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@RedAlertPercent int = 5,
@YellowAlertPercent int = 10,
@EmailCode dbo.EmailCode = 'Space_Report',
@Subject varchar(max) = 'Disk Space Report'
if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end
select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode
if @@ROWCOUNT = 0
begin
return
end;
declare @tDb table(
DBName DBName,
Drive Drive,
DBSizeMB INT,
DriveTotalMB INT,
DriveFreeMB INT,
DailyDBUsedKB INT,
DailyDriveUsedKB INT,
DailyDBUsedKB30 INT,
DailyDriveUsedKB30 INT,
DaysLast int,
PercentAlert int --0: OK, 1: Yellow, 2: Red
);
with cte_Detail (DBName, Drive, DBSizeMB, UnallocatedSpaceMB, ReservedKB, DataKB, IndexSizeKB, UnusedKB, DriveTotalMB, DriveFreeMB,
LastRunDate, LastUsedKB, [Days], LastRunDate30, LastUsedKB30, Days30) as
(
select
db.DBName,
dr.Drive,
dbDtl.DBSizeMB,
dbDtl.UnallocatedSpaceMB,
dbDtl.ReservedKB,
dbDtl.DataKB,
dbDtl.IndexSizeKB,
dbDtl.UnusedKB,
dr.TotalSpaceInMB,
drDtl.FreeSpaceInMB,
hist.RunDate,
hist.UsedKB,
DATEDIFF(dd, hist.RunDate, @Date),
hist30.RunDate,
hist30.UsedKB,
DATEDIFF(dd, hist30.RunDate, @Date)
FROM dbo.StoHistoryHeader hdr
JOIN dbo.StoHistoryDbDetail dbDtl on hdr.StoHistoryHeaderId = dbDtl.StoHistoryHeaderId
JOIN dbo.StoDb db on dbDtl.StoDbId = db.StoDbId and db.IsActive = 1
LEFT JOIN dbo.StoDriveDbMap map on db.StoDbId = map.StoDbId and db.IsActive = 1
LEFT JOIN dbo.StoDrive dr on map.StoDriveId = dr.StoDriveId
LEFT JOIN dbo.StoHistoryDriveDetail drDtl on hdr.StoHistoryHeaderId = drDtl.StoHistoryHeaderId
and dr.StoDriveId = drDtl.StoDriveId
CROSS APPLY (select top 1
hdr2.RunDate,
UsedKB = dbDtl2.DataKB + dbDtl2.IndexSizeKB
from dbo.StoHistoryDbDetail dbDtl2
join dbo.StoHistoryHeader hdr2 on dbDtl2.StoHistoryHeaderId = hdr2.StoHistoryHeaderId
where dbDtl2.StoDbId = dbDtl.StoDbId
order by hdr2.RunDate
) hist
CROSS APPLY (select top 1
hdr2.RunDate,
UsedKB = dbDtl2.DataKB + dbDtl2.IndexSizeKB
from dbo.StoHistoryDbDetail dbDtl2
join dbo.StoHistoryHeader hdr2 on dbDtl2.StoHistoryHeaderId = hdr2.StoHistoryHeaderId
where dbDtl2.StoDbId = dbDtl.StoDbId
and hdr2.RunDate >= DATEADD(mm, -1, @Date)
order by hdr2.RunDate
) hist30
where CAST(hdr.RunDate AS DATE) = @Date
), cte_DB (DBName, Drive, DBSizeMB, UsedKB, LastDate, LastUsedKB, DriveTotalMB, DriveFreeMB, DailyDbUsedKB, DailyDbUsedKB30) as
(
select
cte.DBName,
cte.Drive,
cte.DBSizeMB,
CurrentSize = cte.DataKB + cte.IndexSizeKB,
cte.LastRunDate,
cte.LastUsedKB,
cte.DriveTotalMB,
cte.DriveFreeMB,
DailyDbUsedKB = CASE WHEN (cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB > 0
THEN ((cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB) / [Days]
ELSE 0 END,
DailyDbUsedKB30 = CASE WHEN (cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB30 > 0
THEN ((cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB30) / [Days30]
ELSE 0 END
from cte_Detail cte
), cte_Drive (Drive, DriveTotalMB, DriveFreeMB, DailyDriveUsedKB, DailyDriveUsedKB30, DaysLast) as
(
select
Drive,
MAX(DriveTotalMB),
MAX(DriveFreeMB),
SUM(DailyDbUsedKB),
SUM(DailyDbUsedKB30),
DaysLast = CASE WHEN SUM(DailyDbUsedKB30 * 30.0 / 1000.0) < 1.0 THEN NULL
ELSE MAX(DriveFreeMB) / SUM(DailyDbUsedKB30 / 1000.0) END
from cte_DB
group by Drive
)
insert into @tDb (DBName, Drive, DBSizeMB, DriveTotalMB, DriveFreeMB, DailyDBUsedKB, DailyDriveUsedKB, DailyDBUsedKB30, DailyDriveUsedKB30, DaysLast, PercentAlert)
select
db.DBName,
db.Drive,
db.DBSizeMB,
db.DriveTotalMB,
db.DriveFreeMB,
db.DailyDbUsedKB,
dr.DailyDriveUsedKB,
db.DailyDBUsedKB30,
dr.DailyDriveUsedKB30,
dr.DaysLast,
PercentAlert = case when dr.DriveTotalMB = 0 then 0
when dr.DriveFreeMB * 100.0 / dr.DriveTotalMB < @YellowAlertPercent then 1
when dr.DriveFreeMB * 100.0 / dr.DriveTotalMB < @RedAlertPercent then 2
else 0 end
from cte_DB db
join cte_Drive dr on db.Drive = dr.Drive
set @Subject = 'Server Space Report for ' + convert(varchar(10), @Date, 120)
--select
-- [@bgcolor] = CASE when DaysLast < 30 then '#FF0000' --red/#E41B17
-- when DaysLast < 90 then '#FFFF00' --yellow/#EAC117
-- when DaysLast >= 90 then '#00FF00' --green/#4AA02C
-- ELSE '#800080' --coffee/#4AA02C
-- END,
-- td = Drive, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DriveTotalMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DriveFreeMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- FreePercent = CAST(CAST(DriveFreeMB * 100.0 / DriveTotalMB AS numeric(36, 1)) AS VARCHAR) + '%', '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = dbo.fnDaysLast(DaysLast), ''
--from
--(
-- select
-- Drive,
-- DriveTotalMB = MAX(DriveTotalMB),
-- DriveFreeMB = MAX(DriveFreeMB),
-- DailyDriveUsedKB = MAX(DailyDriveUsedKB ),
-- DailyDriveUsedKB30 = MAX(DailyDriveUsedKB30),
-- DaysLast = MAX(DaysLast)
-- from @tDb
-- group by Drive
--) t
--order by CASE when DaysLast < 30 then 1
-- when DaysLast < 90 then 2
-- ELSE 3
-- END, Drive
set @text =
CAST( (
select
[@bgcolor] = CASE when DaysLast < 30 or PercentAlert = 2 then '#FF0000' --red/#E41B17
when DaysLast < 90 or PercentAlert = 1 then '#FFFF00' --yellow/#EAC117
when DaysLast >= 90 then '#00FF00' --green/#4AA02C
ELSE '#2ECC40' --coffee/#4AA02C/#800080
END,
color = CASE when DaysLast < 30 then 'white' else 'black' END,
td = Drive, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DriveTotalMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DriveFreeMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
FreePercent = CAST(CAST(DriveFreeMB * 100.0 / DriveTotalMB AS numeric(36, 1)) AS VARCHAR) + '%', '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = dbo.fnDaysLast(DaysLast), ''
from
(
select
Drive,
DriveTotalMB = MAX(DriveTotalMB),
DriveFreeMB = MAX(DriveFreeMB),
DailyDriveUsedKB = MAX(DailyDriveUsedKB ),
DailyDriveUsedKB30 = MAX(DailyDriveUsedKB30),
DaysLast = MAX(DaysLast),
PercentAlert = MAX(PercentAlert)
from @tDb
group by Drive
UNION ALL
select
StoDrive.Drive,
DriveTotalMB = CAST(StoDrive.TotalSpaceInMB AS INT),
DriveFreeMB = CAST(drDtl.FreeSpaceInMB AS INT),
DailyDriveUsedKB = NULL,
DailyDriveUsedKB30 = NULL,
DaysLast = NULL,
PercentAlert = case when StoDrive.TotalSpaceInMB = 0 then 0
when drDtl.FreeSpaceInMB * 100.0 / StoDrive.TotalSpaceInMB < @YellowAlertPercent then 1
when drDtl.FreeSpaceInMB * 100.0 / StoDrive.TotalSpaceInMB < @RedAlertPercent then 2
else 0 end
from dbo.StoDrive
JOIN dbo.StoHistoryHeader hdr on CAST(hdr.RunDate AS DATE) = @Date
JOIN dbo.StoHistoryDriveDetail drDtl on hdr.StoHistoryHeaderId = drDtl.StoHistoryHeaderId and StoDrive.StoDriveId = drDtl.StoDriveId
where StoDrive.Drive not in (select Drive from @tDb)
and StoDrive.IsActive = 1
) t
order by CASE when DaysLast < 30 or PercentAlert = 2 then 1
when DaysLast < 90 or PercentAlert = 1 then 2
ELSE 3
END, Drive
for xml path( 'tr' ), type
) as varchar(max) )
set @body = 'The following summary displays space usage on Phone Channel Data Warehouse Server (i.e.: CRSDPSSCA1IDCS\PC):
'
+ '
+ '
' + '
' + '
' + '
' + '
' + '
+ '' +
+ '
(MB)
(MB)
(%)
(MB)
(MB)
+ replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
--select
-- [@bgcolor] = CASE when DaysLast < 30 then '#FF0000' --red/#E41B17
-- when DaysLast < 90 then '#FFFF00' --yellow/#EAC117
-- when DaysLast >= 90 then '#00FF00' --green/#4AA02C
-- ELSE '#800080' --green/#4AA02C
-- END,
-- td = DBName, '',
-- td = Drive, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DBSizeMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB * 30 / 1000.0 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB30 * 30 / 1000.0 AS MONEY), 1), '.00' ,''), '',
-- color = CASE when DaysLast >= 90 then 'close' else 'close' END
--from @tDb
--order by CASE when DaysLast < 30 then 1
-- when DaysLast < 90 then 2
-- ELSE 3
-- END, DBName
set @text =
CAST( (
select
[@bgcolor] = CASE when DaysLast < 30 or PercentAlert = 2 then '#FF0000' --red/#E41B17
when DaysLast < 90 or PercentAlert = 1 then '#FFFF00' --yellow/#EAC117
when DaysLast >= 90 then '#00FF00' --green/#4AA02C
ELSE '#2ECC40' --green/#4AA02C
END,
color = CASE when DaysLast < 30 then 'white' else 'black' END,
td = DBName, '',
td = Drive, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DBSizeMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
color = CASE when DaysLast >= 90 then 'close' else 'close' END
from @tDb
order by CASE when DaysLast < 30 or PercentAlert = 2 then 1
when DaysLast < 90 or PercentAlert = 1 then 2
ELSE 3
END, DBName
for xml path( 'tr' ), type
) as varchar(max) )
set @body = @body + '
The following summary displays disk usage for all databases:
'
+ '
+ '' +
+ '
(MB)
+ '
(MB)
(MB)
' + replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
set @body = REPLACE(@body, '
')
--print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
--------------------------------------------------------------------------------
GO
--exec uspEmailStorageStatus '2018-04-03'
/*
declare @date date = '2018-02-05'
exec uspEmailStorageStatus @date
*/
--select * from dbo.CtsJob
Subscribe to:
Posts (Atom)