Ashok Kumar SQL Development and DBA Adminstrations Techi Blog : Script/Query to Monitor Health Check List of SQL Server through DB Mail

Script/Query to Monitor Health Check List of SQL Server through DB Mail

We are need configure db mail and use below query in step 1 to get health check list

now we are going to execute job as see below

once job executed we got mail as see below

Script:

DECLARE @ServerIP VARCHAR(100), @Project VARCHAR(100),
@Recepients VARCHAR(2000), @MailProfile VARCHAR(100),
@Owner VARCHAR(200)

SET @ServerIP = '192.168.43.168' -- SQL Server 2012 Database Server IP Address
set @Project = 'HealthCheck List' -- Name of project or client
set @Recepients = 'ashokbtech07@gmail.com' -- Recepient(s) of this email (; separated in case of multiple recepients).
set @MailProfile = 'TestGmail' -- Mail profile name which exists on the target database server
set @Owner = 'Test'

SET NOCOUNT ON

SET QUOTED_IDENTIFIER ON

/* Drop all the temp tables(not necessary at all as local temp tables get dropped as soon as session is released,
however, good to follow this practice). */

If exists (select * from tempdb.sys.all_objects where name like '#jobs_status%' )
BEGIN
DROP TABLE #jobs_status
END

If exists (select * from tempdb.sys.all_objects where name like '#diskspace%' )
BEGIN
DROP TABLE #diskspace
END

If exists (select * from tempdb.sys.all_objects where name like '#url%' )
BEGIN
DROP TABLE #url
END

If exists (select * from tempdb.sys.all_objects where name like '#dirpaths%' )
BEGIN
DROP TABLE #dirpaths
END

-- Create the temp tables which will be used to hold the data.
CREATE TABLE #url
(
idd INT IDENTITY (1,1),
url VARCHAR(1000)
)

CREATE TABLE #dirpaths
(
files VARCHAR(2000)
)

--CREATE TABLE #diskspace
--(
-- drive VARCHAR(200),
-- diskspace INT
--)

-- This table will hold data from sp_help_job (System sp in MSDB database)
/*
CREATE TABLE #jobs_status
(
job_id UNIQUEIDENTIFIER,
originating_server NVARCHAR(30),
name SYSNAME,
enabled TINYINT,
description NVARCHAR(512),
start_step_id INT,
category SYSNAME,
owner SYSNAME,
notify_level_eventlog INT,
notify_level_email INT,
notify_level_netsend INT,
notify_level_page INT,
notify_email_operator SYSNAME,
notify_netsend_operator SYSNAME,
notify_page_operator SYSNAME,
delete_level INT,
date_created DATETIME,
date_modified DATETIME,
version_number INT,
last_run_date INT,
last_run_time INT,
last_run_outcome INT,
next_run_date INT,
next_run_time INT,
next_run_schedule_id INT,
current_execution_status INT,
current_execution_step SYSNAME,
current_retry_attempt INT,
has_step INT,
has_schedule INT,
has_target INT,
type INT
)
*/

-- To insert data in couple of temp tables created above.
-- INSERT #diskspace(drive, diskspace) EXEC xp_fixeddrives
SELECT DISTINCT dovs.volume_mount_point AS Drive,
CONVERT(decimal,dovs.available_bytes/1048576/1024) AS FreeSpaceInGB,
convert(decimal,dovs.total_bytes/1048576/1024) as TotalSpaceInGB,
cast(CONVERT(decimal,dovs.available_bytes/1048576/1024)/convert(decimal,dovs.total_bytes/1048576/1024)*100 as decimal(38,2)) as FreeSpaceInPct
into #diskspace
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInGB ASC

select sj.job_id, sj.name, sc.name as Category, sj.Enabled, sjs.last_run_outcome,
(SELECT top 1 endTime = CONVERT
( DATETIME, RTRIM(run_date)) + ( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration) / 216e4
FROM msdb..sysjobhistory sjh where sjh.job_id = sj.job_id order by run_date desc, run_time desc) as last_run_date
into #jobs_status
from msdb..sysjobs sj
join msdb..syscategories sc
on sj.category_id = sc.category_id
join msdb.dbo.sysjobservers sjs
on sjs.job_id = sj.job_id

-- Variable declaration
DECLARE @TableHTML VARCHAR(MAX),
@StrSubject VARCHAR(100),
@Oriserver VARCHAR(100),
@Version VARCHAR(250),
@Edition VARCHAR(100),
@ISClustered VARCHAR(100),
@SP VARCHAR(100),
@ServerCollation VARCHAR(100),
@SingleUser VARCHAR(5),
@LicenseType VARCHAR(100),
@StartDate DATETIME,
@EndDate DATETIME,
@Cnt int,
@URL varchar(1000),
@Str varchar(1000)

-- Variable Assignment
SELECT @Version = @@version
SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))
--SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME)
-- SELECT @StartDate = @StartDate - 1
SELECT @StartDate = getdate()-1
SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME)
SET @Cnt = 0

IF serverproperty('IsClustered') = 0
BEGIN
SELECT @ISClustered = 'No'
END
ELSE
BEGIN
SELECT @ISClustered = 'YES'
END

SELECT @SP = CONVERT(VARCHAR(100), SERVERPROPERTY ('productlevel'))
SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY ('Collation'))
SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType'))
SELECT @SingleUser = CASE SERVERPROPERTY ('IsSingleUser')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE
'null' END
SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('computernamephysicalnetbios'))
SELECT @strSubject = 'Production DB Server Daily Health SQL Server Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'

SET @TableHTML =
'Server Info
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" id="AutoNumber1">
<tr>
<td bgcolor="#000080">
Server Name</td>
<td bgcolor="#000080">
Instance Name</td>
<td bgcolor="#000080">
Edition</td>
<td width="60%" bgcolor="#000080">
Version</td>
<td bgcolor="#000080">
IsClustered</td>
</tr>
<tr>
<td>' + @OriServer +'</td>
<td>' + @@servername +'</td>
<td>' + @edition +'</td>
<td>' + @version +'</td>
<td>' + @isclustered +'</td>
</tr>
</table>'

SELECT
@TableHTML = @TableHTML +
' 
Disk Stats
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="50%" border="1">
<tr>
<td bgColor="#000080" height="15">
Drive</td>
<td bgColor="#000080" height="15">
Free Space (GB)</td>
<td bgColor="#000080" height="15">
Total Space (GB)</td>
<td bgColor="#000080" height="15">
Free Space (%)</td>
</tr>'

SELECT
@TableHTML = @TableHTML +
'<tr><td>' + ISNULL(CONVERT(VARCHAR(100), drive), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), FreeSpaceInGB), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), TotalSpaceInGB), '') +'</td>' +
'<td'+case when FreeSpaceInPct < 15.00 then ' style="background-color:#F00;"' else ' style="background-color:#0F0;"' end +'>' + ISNULL(CONVERT(VARCHAR(100), FreeSpaceInPct), '') +'</td></tr>'
FROM
#diskspace
SELECT @TableHTML = @TableHTML + '</table>'

--========================---CPU stats
If exists (select * from tempdb.sys.all_objects where name like '#cpu_usage%' )
drop table #cpu_usage

declare @ts_now bigint
select @ts_now = ms_ticks from sys.dm_os_sys_info
select dateadd (ms, (y.[timestamp] -@ts_now), GETDATE()) as EventTime,SQLProcessUtilization,SystemIdle, 100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
into #cpu_usage
from ( select record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
as SystemIdle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int') as SQLProcessUtilization,
timestamp from ( select timestamp, convert(xml, record) as record from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%') as x
) as y
order by record_id desc

--select * from #cpu_usage order by SQLProcessUtilization desc

SELECT @TableHTML = @TableHTML + ' 
CPU Usage (%)
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
EventTime</th>
<th align="left" bgColor="#000080">
SQLProcessUtilization</th>
<th align="left" bgColor="#000080">
SystemIdle</th>
<th align="left" bgColor="#000080">
OtherProcessUtilization</th>
</tr>'
SELECT top 10
@TableHTML = @TableHTML +
'<tr><td>' + ISNULL(CONVERT(VARCHAR(100), EventTime), '') +'</td>' +
'<td'+case when SQLProcessUtilization > 80 then ' style="background-color:#F00;"' else ' style="background-color:#0F0;"' end +'>' + ISNULL(CONVERT(VARCHAR(100), SQLProcessUtilization), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), SystemIdle), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), OtherProcessUtilization), '') +'</td></tr>'
FROM
#cpu_usage order by SQLProcessUtilization desc
SELECT @TableHTML = @TableHTML + '</table>'
--================cpu stats ends
--===============memory stats
If exists (select * from tempdb.sys.all_objects where name like '#memory_stats%' )
drop table #memory_stats

declare @bufferCacheHit decimal
SELECT @bufferCacheHit=cast((a.cntr_value * 1.0 / b.cntr_value) * 100.0 as decimal(38,2))
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'
SELECT @bufferCacheHit as BufferCacheHitRatio, physical_memory_kb/1024/1024 as PhysicaMemoryInGB, committed_kb/1024/1024 as SQLCommittedInGB
into #memory_stats
FROM sys.dm_os_sys_info

SELECT @TableHTML = @TableHTML + ' 
Memory Usage
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
Buffer Cache Hit Ratio (%)</th>
<th align="left" bgColor="#000080">
Total PhysicalMemory (GB)</th>
<th align="left" bgColor="#000080">
SQLCommittedMemory (GB)</th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr><td>' + ISNULL(CONVERT(VARCHAR(100), BufferCacheHitRatio), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), PhysicaMemoryInGB), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), SQLCommittedInGB), '') +'</td></tr>'
FROM
#memory_stats
SELECT @TableHTML = @TableHTML + '</table>'

--===============memory stats ends
SELECT @TableHTML = @TableHTML + ' 
Job Status
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="100%" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" width="432" bgColor="#000080">
Job Name</th>
<th align="left" width="85" bgColor="#000080">
Last Run</th>
<th align="left" width="183" bgColor="#000080">
Category</th>
<th align="left" width="136" bgColor="#000080">
Last Run Date</th>
<th align="left" width="136" bgColor="#000080">
Execution Time (Mi)</th>
</tr>'

SELECT
@TableHTML = @TableHTML + '<tr><td>' +
ISNULL(CONVERT(VARCHAR(100), A.name), '') +'</td>' +
CASE last_run_outcome
WHEN 0 THEN '<td bgColor="#ff0000"><blink>
<a href="mailto:servicedesk@mycompany.com?subject=Job failure - ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101) +'&cc=db.support@mycompany.com&body = SD please log this call to DB support,' + '%0A %0A' + '<<' + ISNULL(CONVERT(VARCHAR(100), name),'''') + '>> Job Failed on ' + @OriServer + '(' + @ServerIP + ')'+ '.' +'%0A%0A Regards,'+'">Failed</a></blink></td>'
WHEN 1 THEN '<td>Success</td>'
WHEN 3 THEN '<td>Cancelled</td>'
WHEN 5 THEN '<td>Unknown</td>'
ELSE '<td>Other</td>'
END +
'<td>' + ISNULL(CONVERT(VARCHAR(100), A.category),'') + '</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),'') + '</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),'') +'</td> </tr>'
FROM
#jobs_status A
inner join (
select
A.job_id, A.start_execution_date,
datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes
from
msdb..sysjobactivity A
inner join (
select
max(session_id) sessionid,
job_id
from
msdb..sysjobactivity
group by
job_id
) B on a.job_id = B.job_id and a.session_id = b.sessionid
inner join (
select
distinct name,
job_id
from
msdb..sysjobs
) C on A.job_id = c.job_id
) X on A.job_id = X.job_id
where enabled = 1
ORDER BY
last_run_date DESC
--select * from #jobs_status
SET @TableHTML = @TableHTML +
'</table>'

--====================database details
SELECT
@TableHTML = @TableHTML +
' 
Databases
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="100%" border="1">
<tr>
<td width="35%" bgColor="#000080" height="15">
Name</td>
<td width="23%" bgColor="#000080" height="15">
CreatedDate</td>
<td width="23%" bgColor="#000080" height="15">
DB Size(GB)</td>
<td width="30%" bgColor="#000080" height="15">
State</td>
<td width="50%" bgColor="#000080" height="15">
RecoveryModel</td>
</tr>'

select
@TableHTML = @TableHTML +
'<tr><td>' + ISNULL(name, '') +'</td>' +
'<td>' + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + '-' + CONVERT(VARCHAR(4),DATEPART(yy,create_date)) +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), '') +'</td>' +
'<td>' + ISNULL(state_desc, '') +'</td>' +
'<td>' + ISNULL(recovery_model_desc, '') +'</td></tr>'
from
sys.databases MST
inner join (
--select b.name [LOG_DBNAME],
-- CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(10,2),(a.size * 8)) /1024)/1024) [Total Size GB]
-- from sys.sysaltfiles A
-- inner join sys.databases B on A.dbid = B.database_id
-- group by b.name

select b.name [LOG_DBNAME],
CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(10,2),a.size) )*8/1024/1024) [Total Size GB]
--CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(10,2),(a.size * 8)) /1024)) [Total Size MB]
from sys.sysaltfiles A
inner join sys.databases B
on A.dbid = B.database_id
group by b.name
)AA on AA.[LOG_DBNAME] = MST.name
order by
MST.name

SET @TableHTML = @TableHTML +
'</table>'

--=======================database details ends
--=======================index fragmentation
If exists (select * from tempdb.sys.all_objects where name like '#db_frag%' )
drop table #db_frag
create table #db_frag
( DatabaseName varchar(100),
ObjectName varchar(100),
indexName varchar(100),
avg_fragmentation_percent float,
page_count int,
IndexType varchar(100),
Action_Required varchar(100) default 'NA'
)

insert into #db_frag (DatabaseName,ObjectName, indexName,avg_fragmentation_percent,page_count,IndexType)

exec master.sys.sp_MSforeachdb ' USE [?]

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,
b.name as IndexName,
avg_fragmentation_in_percent, page_count, index_type_desc
-- , record_count, avg_page_space_used_in_percent --(null in limited)
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE b.index_id <> 0 and avg_fragmentation_in_percent > 70
and page_count > 1000
'

update #db_frag
set Action_Required ='Rebuild'
where avg_fragmentation_percent >30

update #db_frag
set Action_Required ='Rorganize'
where avg_fragmentation_percent <30 and avg_fragmentation_percent >10

SELECT @TableHTML = @TableHTML + ' 
Index Fragmentation (> 70%)
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
Database Name</th>
<th align="left" bgColor="#000080">
Object Name</th>
<th align="left" bgColor="#000080">
Index Name</th>
<th align="left" bgColor="#000080">
Avg Frag (%)</th>
<th align="left" bgColor="#000080">
Page Count</th>
<th align="left" bgColor="#000080">
Type</th>
<th align="left" bgColor="#000080">
ActionRequired</th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr style="color:#F00"><td>' + ISNULL(CONVERT(VARCHAR(100), DatabaseName), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), ObjectName), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), indexName), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), avg_fragmentation_percent), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), page_count), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), IndexType), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), Action_Required), '') +'</td></tr>'
FROM
#db_frag
SELECT @TableHTML = @TableHTML + '</table>'

--=========================index fragmentation ends
--------
--=========================Mirror Status
If exists (select * from tempdb.sys.all_objects where name like '#mirror_status%' )
BEGIN
DROP TABLE #mirror_status
END
CREATE TABLE #mirror_status
(
name varchar(30),
mdbid int,
status varchar (30),
partnername varchar(50)
)
INSERT into #mirror_status(name, mdbid,[status],partnername)
SELECT DB_NAME(database_id),database_id,mirroring_state_desc,mirroring_partner_name
FROM sys.database_mirroring
WHERE mirroring_role is NOT NULL

if exists (select * from #mirror_status)
begin
SELECT
@TableHTML = @TableHTML +
' Database Mirroring Status
</table><table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0"

width="61%" border="1">
<tr>
<td width="15%" bgColor="#000080" height="15">
Database Name</td>
<td width="15%" bgColor="#000080" height="15">
Database ID</td>
<td width="30%" bgColor="#000080" height="15">
Mirror State</td>
<td width="70%" bgColor="#000080" height="15">
Mirror Partner Name </td>

</tr>
 
'

SELECT
@TableHTML = @TableHTML +
'<tr><td>' + ISNULL(CONVERT(VARCHAR(100),

ms.name), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100),

ms.mdbid), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100),

ms.status), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100),

ms.partnername), '') +
+'</td></tr>'
FROM
#mirror_status ms

SELECT @TableHTML = @TableHTML + '</table>'
end
else
begin
SELECT
@TableHTML = @TableHTML +
' Database Mirroring Status (Mirroring not configured)'
end
--============Mirror status ends
-------
--============REPLICATION status
if exists(select * from master.sys.sysservers where srvname like 'repl_distributor')
begin

If exists (select * from tempdb.sys.all_objects where name like '#replication_status%' )
BEGIN
DROP TABLE #replication_status
END
CREATE TABLE #replication_status(
[agent_name] [sysname] NOT NULL,
[PublicationType] [varchar](13) NULL,
[AgentType] [varchar](12) NULL,
[Status] [varchar](9) NULL,
[Warning] [varchar](20) NULL,
[last_distsync] [datetime] NULL,
[retention] [int] NULL,
[avg_latency] [int] NULL,
[average_runspeedPerf] [int] NULL
)

if not exists (select * from master.sys.sysservers where srvname like 'repl_distributor' and datasource like @@SERVERNAME)
begin
declare @distributor varchar(50), @cmd varchar(max)
select @distributor = datasource from master.sys.sysservers where srvname like 'repl_distributor'
print 'Distributor - ' + @distributor

set @cmd = 'SELECT agent_name, case publication_type
when 0 then ''Transactional''
when 1 then ''Snapshot''
when 2 then ''Merge''
end as PublicationType,
case agent_type
when 1 then ''Snapshot''
when 2 then ''Log Reader''
when 3 then ''Distribution''
when 4 then ''Merge''
when 9 then ''Queue Reader''
end as AgentType,
case status
when 1 then ''Started''
when 2 then ''Succeeded''
when 3 then ''Progress''
when 4 then ''Idle''
when 5 then ''Retrying''
when 6 then ''Failed''
end as Status,
case warning
when 1 then ''expiration''
when 2 then ''latency''
when 4 then ''mergeexpiration''
when 16 then ''mergeslowrunduration''
when 32 then ''mergefastrunspeed''
when 64 then ''mergeslowrunspeed''
end as Warning,
last_distsync, retention, avg_latency, average_runspeedPerf
FROM OPENROWSET(''SQLNCLI'', ''Server=' + @distributor+';Trusted_Connection=yes;'',
''select * from distribution.dbo.MSreplication_monitordata'') AS a'

insert into #replication_status
exec (@cmd)

end
else
begin
insert into #replication_status
select agent_name, case publication_type
when 0 then 'Transactional'
when 1 then 'Snapshot'
when 2 then 'Merge'
end as PublicationType,
case agent_type
when 1 then 'Snapshot'
when 2 then 'Log Reader'
when 3 then 'Distribution'
when 4 then 'Merge'
when 9 then 'Queue Reader'
end as AgentType,
case status
when 1 then 'Started'
when 2 then 'Succeeded'
when 3 then 'Progress'
when 4 then 'Idle'
when 5 then 'Retrying'
when 6 then 'Failed'
end as Status,
case warning
when 1 then 'expiration'
when 2 then 'latency'
when 4 then 'mergeexpiration'
when 16 then 'mergeslowrunduration'
when 32 then 'mergefastrunspeed'
when 64 then 'mergeslowrunspeed'
end as Warning,
last_distsync, retention, avg_latency, average_runspeedPerf
from distribution.dbo.MSreplication_monitordata
end

SELECT
@TableHTML = @TableHTML +
' 
Replication Statistics
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
AgentName</th>
<th align="left" bgColor="#000080">
PublicationType</th>
<th align="left" bgColor="#000080">
AgentType</th>
<th align="left" bgColor="#000080">
Status</th>
<th align="left" bgColor="#000080">
Warning</th>
<th align="left" bgColor="#000080">
Last_Dist_Sync</th>
<th align="left" bgColor="#000080">
Retention</th>
<th align="left" bgColor="#000080">
avg_latency</th>
<th align="left" bgColor="#000080">
average_runspeedPerf</th>
</tr>'

SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td>' + isnull(cast(agent_name as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(PublicationType as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(AgentType as varchar(500)),'') +'</td>' +
'<td>' + isnull(cast([Status] as varchar(50)),'') +'</td>' +
'<td>' + isnull(cast([Warning] as varchar(200)),'') +'</td>' +
'<td>' + isnull(cast(last_distsync as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast([retention] as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(avg_latency as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(average_runspeedPerf as varchar(100)),'') +'</td>' +
'</tr>'
FROM #replication_status

SELECT
@TableHTML = @TableHTML + N'</table>'

end

else
begin
SELECT
@TableHTML = @TableHTML +
' Replication Status (Replication not configured / No publications)'
end

--============replication status ends
-----------------------------

-- ======== DB Log Shipping Monitor

if exists (select * from msdb..log_shipping_monitor_primary)
begin
SELECT
@TableHTML = @TableHTML +
' 
Log Shipping Stats 
Local Primaries
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
Instance</th>
<th align="left" bgColor="#000080">
Database</th>
<th align="left" bgColor="#000080">
BackupThreshold</th>
<th align="left" bgColor="#000080">
LastBackup</th>
<th align="left" bgColor="#000080">
LastBackupFile</th>
</tr>'

SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td>' + isnull(cast(primary_server as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(primary_database as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(backup_threshold as varchar(10)),'') +'</td>' +
'<td>' + isnull(cast(last_backup_date as varchar(50)),'') +'</td>' +
'<td>' + isnull(cast(last_backup_file as varchar(300)),'') +'</td>' +
'</tr>'
FROM msdb..log_shipping_monitor_primary

SELECT
@TableHTML = @TableHTML + N'</table>'
end

if exists (select * from msdb..log_shipping_monitor_secondary)
begin
SELECT
@TableHTML = @TableHTML +
' 
Local Secondaries
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
PrimaryInstance</th>
<th align="left" bgColor="#000080">
SecondaryInstance</th>
<th align="left" bgColor="#000080">
Database</th>
<th align="left" bgColor="#000080">
RestoreThreshold</th>
<th align="left" bgColor="#000080">
LastRestore</th>
<th align="left" bgColor="#000080">
LastRestoredFile</th>
</tr>'

SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td>' + isnull(cast(primary_server as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(secondary_server as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(primary_database as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(restore_threshold as varchar(10)),'') +'</td>' +
'<td>' + isnull(cast(last_restored_date as varchar(50)),'') +'</td>' +
'<td>' + isnull(cast(last_restored_file as varchar(300)),'') +'</td>' +
'</tr>'
FROM msdb..log_shipping_monitor_secondary

SELECT
@TableHTML = @TableHTML + N'</table>'
end
-- Fetch from remote secondaries
if exists (select * from msdb..log_shipping_primary_secondaries)
begin
declare @remotesecondary sysname;
if exists (select name from tempdb..sysobjects where name like '%#remoteLSSecondaries%')
drop table #remoteLSSecondaries

select secondary_server into #remoteLSSecondaries from msdb..log_shipping_primary_secondaries

select top 1 @remotesecondary = secondary_server from #remoteLSSecondaries

while(@remotesecondary is not null)
begin
set @cmd = 'select primary_server, secondary_server,
secondary_database, restore_threshold,
last_restored_date, last_restored_file
FROM OPENROWSET(''SQLNCLI'', ''Server=' + @remotesecondary+';Trusted_Connection=yes;'',
''select * from msdb..log_shipping_monitor_secondary'') AS a'

if exists (select name from tempdb..sysobjects where name like '%#remoteLSStats%')
drop table #remoteLSStats

create table #remoteLSStats
(
primary_server sysname, secondary_server sysname,
secondary_database sysname, restore_threshold int,
last_restored_date varchar(50), last_restored_file varchar(500)
)

insert into #remoteLSStats
exec (@cmd)
delete from #remoteLSSecondaries where secondary_server = @remotesecondary
set @remotesecondary = NULL
select top 1 @remotesecondary = secondary_server from #remoteLSSecondaries
end

--select * from #remoteLSStats
SELECT
@TableHTML = @TableHTML +
' 
Remote Secondaries
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
PrimaryInstance</th>
<th align="left" bgColor="#000080">
SecondaryInstance</th>
<th align="left" bgColor="#000080">
Database</th>
<th align="left" bgColor="#000080">
RestoreThreshold</th>
<th align="left" bgColor="#000080">
LastRestore</th>
<th align="left" bgColor="#000080">
LastRestoredFile</th>
</tr>'

SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td>' + isnull(cast(primary_server as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(secondary_server as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(secondary_database as varchar(100)),'') +'</td>' +
'<td>' + isnull(cast(restore_threshold as varchar(10)),'') +'</td>' +
'<td>' + isnull(cast(last_restored_date as varchar(50)),'') +'</td>' +
'<td>' + isnull(cast(last_restored_file as varchar(300)),'') +'</td>' +
'</tr>'
FROM #remoteLSStats

SELECT
@TableHTML = @TableHTML + N'</table>'

end

-- ========== DB Log shipping monitor ends

-- Code for SQL Server Database Backup Stats
SELECT
@TableHTML = @TableHTML +
' 
Backup Stats
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" width="91" bgColor="#000080">
Date</th>
<th align="left" width="105" bgColor="#000080">
Database</th>
<th align="left" width="165" bgColor="#000080">
File Name</th>
<th align="left" width="75" bgColor="#000080">
Type</th>
<th align="left" width="165" bgColor="#000080">
Start Time</th>
<th align="left" width="165" bgColor="#000080">
End Time</th>
<th align="left" width="136" bgColor="#000080">
Size(GB)</th>
</tr>'

SELECT
@TableHTML = @TableHTML +
'<tr>
<td>' + ISNULL(CONVERT(VARCHAR(2), DATEPART(dd,MST.backup_start_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm, MST.backup_start_date)) + '-' + CONVERT(VARCHAR(4), DATEPART(yyyy, MST.backup_start_date)),'') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), MST.database_name), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(100), MST.name), '') +'</td>' +
CASE Type
WHEN 'D' THEN '<td>' + 'Full' +'</td>'
WHEN 'I' THEN '<td>' + 'Differential' +'</td>'
WHEN 'L' THEN '<td>' + 'Log' +'</td>'
WHEN 'F' THEN '<td>' + 'File or Filegroup' +'</td>'
WHEN 'G' THEN '<td>' + 'File Differential' +'</td>'
WHEN 'P' THEN '<td>' + 'Partial' +'</td>'
WHEN 'Q' THEN '<td>' + 'Partial Differential' +'</td>'
ELSE '<td>' + 'Unknown' +'</td>'
END +
'<td>' + ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), '') +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), '') +'</td>' +
'</tr>'
FROM
msdb..backupset MST
WHERE
MST.backup_start_date BETWEEN @StartDate AND @EndDate
ORDER BY
MST.backup_start_date DESC

SELECT @TableHTML = @TableHTML + '</table>'

-- Code for physical database backup file present on disk
INSERT #url
SELECT DISTINCT
SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) - CHARINDEX('\', REVERSE(BMF.physical_device_name), 0))
from
msdb..backupset MST
inner join msdb..backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id
where
MST.backup_start_date BETWEEN @startdate AND @enddate

select @Cnt = COUNT(*) FROM #url

WHILE @Cnt >0
BEGIN

SELECT @URL = url FROM #url WHERE idd = @Cnt
SELECT @Str = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D'''

INSERT #dirpaths SELECT 'PATH: ' + @URL
INSERT #dirpaths

EXEC (@Str)

INSERT #dirpaths values('')

SET @Cnt = @Cnt - 1

end

DELETE FROM #dirpaths WHERE files IS NULL

select
@TableHTML = @TableHTML +
' 
Physical Backup Files
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" width="91" bgColor="#000080">
Physical Files</th>
</tr>'

SELECT
@TableHTML = @TableHTML + '<tr>' +
CASE SUBSTRING(files, 1, 5)
WHEN 'PATH:' THEN '<td bgcolor = "#D7D7D7">' + files + '</td>'
ELSE
'<td>' + files + '</td>'
END +
'</tr></table>'
FROM
#dirpaths

SELECT
@TableHTML = @TableHTML +
' 
<hr color="#000000" size="1">

Thanks
and Regards,
DB
Support Team
 '

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients=@Recepients,
@subject = @strSubject,
@body = @TableHTML,
@body_format = 'HTML' ;

--print @TableHTML

SET NOCOUNT OFF

Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Sunday 9 April 2017

Script/Query to Monitor Health Check List of SQL Server through DB Mail

No comments:

Post a Comment

Recent Comments on this Blog

SQL Question

Visitors Location @ Globe

Featured post

How to Verify Database Backup file is not corrupted one in SQL Server? How to Validate Database Backup in SQL Serer?