SQL Server Monitoring – Automating SQL Server Health Checks (SQL Server 2005 & Above)
DECLARE @ServerIP VARCHAR(100), @Project VARCHAR(100),
@Recepients VARCHAR(2000), @MailProfile VARCHAR(100),
@Owner VARCHAR(200)
SET @ServerIP = '.' -- SQL Server 2012 Database Server IP Address
set @Project = 'Aveva' -- Name of project or client
set @Recepients = 'debadatta.parida@aveva.com' -- Recepient(s) of this email (; separated in case of multiple recepients).
set @MailProfile = 'Dev' -- Mail profile name which exists on the target database server
set @Owner = 'Debadatta'
SET NOCOUNT 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 SunRise Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'
SET @TableHTML =
'<font face="Verdana" size="4">Server Info</font>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" id="AutoNumber1">
<tr>
<td bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
<td bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Instance Name</font></b></td>
<td bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Edition</font></b></td>
<td width="60%" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Version</font></b></td>
<td bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">IsClustered</font></b></td>
</tr>
<tr>
<td><font face="Verdana" size="2">' + @OriServer +'</font></td>
<td><font face="Verdana" size="2">' + @@servername +'</font></td>
<td><font face="Verdana" size="2">' + @edition +'</font></td>
<td><font face="Verdana" size="2">' + @version +'</font></td>
<td><font face="Verdana" size="2">' + @isclustered +'</font></td>
</tr>
</table>'
SELECT
@TableHTML = @TableHTML +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Disk Stats</font>
<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"><b>
<font face="Verdana" size="1" color="#FFFFFF">Drive</font></b></td>
<td bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Free Space (GB)</font></b></td>
<td bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Total Space (GB)</font></b></td>
<td bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Free Space (%)</font></b></td>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), drive), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), FreeSpaceInGB), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), TotalSpaceInGB), '') +'</font></td>' +
'<td'+case when FreeSpaceInPct < 15.00 then ' style="background-color:#F00;"' else ' style="background-color:#0F0;"' end +'><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), FreeSpaceInPct), '') +'</font></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 + '<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4">CPU Usage (%)</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">EventTime</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SQLProcessUtilization</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SystemIdle</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">OtherProcessUtilization</font></th>
</tr>'
SELECT top 10
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), EventTime), '') +'</font></td>' +
'<td'+case when SQLProcessUtilization > 80 then ' style="background-color:#F00;"' else ' style="background-color:#0F0;"' end +'><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), SQLProcessUtilization), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), SystemIdle), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), OtherProcessUtilization), '') +'</font></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 + '<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Memory Usage</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Buffer Cache Hit Ratio (%)</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Total PhysicalMemory (GB)</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SQLCommittedMemory (GB)</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), BufferCacheHitRatio), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), PhysicaMemoryInGB), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), SQLCommittedInGB), '') +'</font></td></tr>'
FROM
#memory_stats
SELECT @TableHTML = @TableHTML + '</table>'
--===============memory stats ends
SELECT @TableHTML = @TableHTML + '<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Job Status</font>
<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">
<font face="Verdana" size="1" color="#FFFFFF">Job Name</font></th>
<th align="left" width="85" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last Run</font></th>
<th align="left" width="183" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Category</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last Run Date</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Execution Time (Mi)</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML + '<tr><td><font face="Verdana" size="1">' +
ISNULL(CONVERT(VARCHAR(100), A.name), '') +'</font></td>' +
CASE last_run_outcome
WHEN 0 THEN '<td bgColor="#ff0000"><b><blink><font face="Verdana" size="2">
<a href="mailto:Debadatta.parida@aveva.com?subject=Job failure - ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101) +'&cc=Debadatta.parida@aveva.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></font></blink></b></td>'
WHEN 1 THEN '<td><font face="Verdana" size="1">Success</font></td>'
WHEN 3 THEN '<td><font face="Verdana" size="1">Cancelled</font></td>'
WHEN 5 THEN '<td><font face="Verdana" size="1">Unknown</font></td>'
ELSE '<td><font face="Verdana" size="1">Other</font></td>'
END +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), A.category),'') + '</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),'') + '</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),'') +'</font></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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Databases</font>
<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"><b>
<font face="Verdana" size="1" color="#FFFFFF">Name</font></b></td>
<td width="23%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">CreatedDate</font></b></td>
<td width="23%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">DB Size(GB)</font></b></td>
<td width="30%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">State</font></b></td>
<td width="50%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">RecoveryModel</font></b></td>
</tr>'
select
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(name, '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + '-' + CONVERT(VARCHAR(4),DATEPART(yy,create_date)) +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(state_desc, '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(recovery_model_desc, '') +'</font></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.database_id
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 + '<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Index Fragmentation (> 70%)</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database Name</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Object Name</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Index Name</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Avg Frag (%)</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Page Count</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Type</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">ActionRequired</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr style="color:#F00"><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), DatabaseName), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), ObjectName), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), indexName), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), avg_fragmentation_percent), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), page_count), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), IndexType), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Action_Required), '') +'</font></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 +
'<br><font face="Verdana" size="4">Database Mirroring Status</font>
</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"><b>
<font face="Verdana" size="1" color="#FFFFFF">Database Name</font></b></td>
<td width="15%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Database ID</font></b></td>
<td width="30%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Mirror State</font></b></td>
<td width="70%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Mirror Partner Name </font></b></td>
</tr>
<p style="margin-top: 1; margin-bottom: 0"> </p>
'
SELECT
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100),
ms.name), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100),
ms.mdbid), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100),
ms.status), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100),
ms.partnername), '') +
+'</font></td></tr>'
FROM
#mirror_status ms
SELECT @TableHTML = @TableHTML + '</table>'
end
else
begin
SELECT
@TableHTML = @TableHTML +
'<br><p><font face="Verdana" size="4">Database Mirroring Status</font> (Mirroring not configured)</p>'
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,
when 2 then ''latency''
case warning
when 1 then ''expiration''
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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Replication Statistics</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">AgentName</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">PublicationType</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">AgentType</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Status</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Warning</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last_Dist_Sync</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Retention</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">avg_latency</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">average_runspeedPerf</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td><font face="Verdana" size="1">' + isnull(cast(agent_name as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(PublicationType as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(AgentType as varchar(500)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast([Status] as varchar(50)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast([Warning] as varchar(200)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_distsync as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast([retention] as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(avg_latency as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(average_runspeedPerf as varchar(100)),'') +'</font></td>' +
'</tr>'
FROM #replication_status
SELECT
@TableHTML = @TableHTML + N'</table>'
end
else
begin
SELECT
@TableHTML = @TableHTML +
'<br><p><font face="Verdana" size="4">Replication Status</font> (Replication not configured / No publications)</p>'
end
--============replication status ends
-----------------------------
-- ======== DB Log Shipping Monitor
if exists (select * from msdb..log_shipping_monitor_primary)
begin
SELECT
@TableHTML = @TableHTML +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Log Shipping Stats</font><br />
<font face="Verdana" size="2">Local Primaries</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Instance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">BackupThreshold</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastBackup</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastBackupFile</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_database as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(backup_threshold as varchar(10)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_backup_date as varchar(50)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_backup_file as varchar(300)),'') +'</font></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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="2">Local Secondaries</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">PrimaryInstance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SecondaryInstance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">RestoreThreshold</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastRestore</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastRestoredFile</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(secondary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_database as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(restore_threshold as varchar(10)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_restored_date as varchar(50)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_restored_file as varchar(300)),'') +'</font></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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="2">Remote Secondaries</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">PrimaryInstance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SecondaryInstance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">RestoreThreshold</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastRestore</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastRestoredFile</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(secondary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(secondary_database as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(restore_threshold as varchar(10)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_restored_date as varchar(50)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_restored_file as varchar(300)),'') +'</font></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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Backup Stats</font>
<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">
<font face="Verdana" size="1" color="#FFFFFF">Date</font></th>
<th align="left" width="105" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database</font></th>
<th align="left" width="165" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">File Name</font></th>
<th align="left" width="75" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Type</font></th>
<th align="left" width="165" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Start Time</font></th>
<th align="left" width="165" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">End Time</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Size(GB)</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + 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)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), MST.database_name), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), MST.name), '') +'</font></td>' +
CASE Type
WHEN 'D' THEN '<td><font face="Verdana" size="1">' + 'Full' +'</font></td>'
WHEN 'I' THEN '<td><font face="Verdana" size="1">' + 'Differential' +'</font></td>'
WHEN 'L' THEN '<td><font face="Verdana" size="1">' + 'Log' +'</font></td>'
WHEN 'F' THEN '<td><font face="Verdana" size="1">' + 'File or Filegroup' +'</font></td>'
WHEN 'G' THEN '<td><font face="Verdana" size="1">' + 'File Differential' +'</font></td>'
WHEN 'P' THEN '<td><font face="Verdana" size="1">' + 'Partial' +'</font></td>'
WHEN 'Q' THEN '<td><font face="Verdana" size="1">' + 'Partial Differential' +'</font></td>'
ELSE '<td><font face="Verdana" size="1">' + 'Unknown' +'</font></td>'
END +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), '') +'</font></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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Physical Backup Files</font>
<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">
<font face="Verdana" size="1" color="#FFFFFF">Physical Files</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML + '<tr>' +
CASE SUBSTRING(files, 1, 5)
WHEN 'PATH:' THEN '<td bgcolor = "#D7D7D7"><b><font face="Verdana" size="1">' + files + '</font><b></td>'
ELSE
'<td><font face="Verdana" size="1">' + files + '</font></td>'
END +
'</tr></table>'
FROM
#dirpaths
SELECT
@TableHTML = @TableHTML +
'<p style="margin-top: 0; margin-bottom: 0"> </p>
<hr color="#000000" size="1">
<!--<p><font face="Verdana" size="2"><b>Server Owner:</b> '+@owner+'</font></p> -->
<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Thanks
and Regards,</font></p>
<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">DB
Support Team</font></p>
<p> </p>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients=@Recepients,
@subject = @strSubject,
@body = @TableHTML,
@body_format = 'HTML' ;
--print @TableHTML
SET NOCOUNT OFF
Changes for All Drive details
DECLARE @ServerIP VARCHAR(100), @Project VARCHAR(100),
@Recepients VARCHAR(2000), @MailProfile VARCHAR(100),
@Owner VARCHAR(200)
SET @ServerIP = '.' -- SQL Server 2012 Database Server IP Address
set @Project = 'Aveva' -- Name of project or client
set @Recepients = 'debadatta.parida@aveva.com' -- Recepient(s) of this email (; separated in case of multiple recepients).
set @MailProfile = 'Dev' -- Mail profile name which exists on the target database server
set @Owner = 'Debadatta'
SET NOCOUNT 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)
)
If exists (select * from tempdb.sys.all_objects where name like '#drives%' )
BEGIN
DROP TABLE #drives
End
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
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 = 'Daily Health Check Report - '+convert(varchar(20),getdate(),100) +' - ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'
SET @TableHTML =
'<font face="Verdana" size="4">Server Info</font>
<table border="1" cellpadding="0" cellspacing="0" width="75%" style="border-collapse: collapse" bordercolor="#111111" id="AutoNumber1">
<tr>
<td bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
<td bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Instance Name</font></b></td>
<td bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Edition</font></b></td>
<td width="60%" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Version</font></b></td>
<td bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">IsClustered</font></b></td>
</tr>
<tr>
<td><font face="Verdana" size="2">' + @OriServer +'</font></td>
<td><font face="Verdana" size="2">' + @@servername +'</font></td>
<td><font face="Verdana" size="2">' + @edition +'</font></td>
<td><font face="Verdana" size="2">' + @version +'</font></td>
<td><font face="Verdana" size="2">' + @isclustered +'</font></td>
</tr>
</table>'
SELECT
@TableHTML = @TableHTML +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Disk Stats</font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="75%" border="1">
<tr>
<td bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Drive</font></b></td>
<td bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Total Space (GB)</font></b></td>
<td bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Free Space (GB)</font></b></td>
<td bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Free Space (%)</font></b></td>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), drive), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), convert(decimal,TotalSize/1024)), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), CONVERT(decimal,FreeSpace/1024)), '') +'</font></td>' +
'<td'+case when cast(CONVERT(decimal,FreeSpace/1024)/convert(decimal,TotalSize/1024)*100 as decimal(38,2)) < 15.00 then ' style="background-color:#F00;"' else ' style="background-color:#0F0;"' end +'><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), cast(CONVERT(decimal,FreeSpace/1024)/convert(decimal,TotalSize/1024)*100 as decimal(38,2))), '') +'</font></td></tr>'
FROM #drives
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 + '<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4">CPU Usage (%)</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" width="75%" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">EventTime</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SQLProcessUtilization</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SystemIdle</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">OtherProcessUtilization</font></th>
</tr>'
SELECT top 10
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), EventTime), '') +'</font></td>' +
'<td'+case when SQLProcessUtilization > 80 then ' style="background-color:#F00;"' else ' style="background-color:#0F0;"' end +'><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), SQLProcessUtilization), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), SystemIdle), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), OtherProcessUtilization), '') +'</font></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
select
total_physical_memory_kb/1024 AS [Total physical Memory MB],
available_physical_memory_kb/1024 AS [Available Physical Memory MB],
total_page_file_kb/1024 AS [Total Page File MB],
available_page_file_kb/1024 AS [Available Page File MB],
cast(100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3))) as decimal(18,2))
AS 'Percentage Used',
system_memory_state_desc
into #memory_stats
from sys.dm_os_sys_memory;
SELECT @TableHTML = @TableHTML + '<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Memory Usage</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" width="75%" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Total physical Memory MB</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Available Physical Memory MB</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Total Page File MB</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Available Page File MB</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Percentage Used</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Comments</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), [Total physical Memory MB]), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), [Available Physical Memory MB]), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), [Total Page File MB]), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), [Available Page File MB]), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), [Percentage Used]), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), system_memory_state_desc), '') +'</font></td></tr>'
FROM
#memory_stats
SELECT @TableHTML = @TableHTML + '</table>'
--===============memory stats ends
SELECT @TableHTML = @TableHTML + '<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Job Status</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="75%" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" width="432" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Job Name</font></th>
<th align="left" width="85" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last Run</font></th>
<th align="left" width="183" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Category</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last Run Date</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Execution Time (Mi)</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML + '<tr><td><font face="Verdana" size="1">' +
ISNULL(CONVERT(VARCHAR(100), A.name), '') +'</font></td>' +
CASE last_run_outcome
WHEN 0 THEN '<td bgColor="#ff0000"><b><blink><font face="Verdana" size="2">
<a href="mailto:Debadatta.parida@aveva.com?subject=Job failure - ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101) +'&cc=Debadatta.parida@aveva.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></font></blink></b></td>'
WHEN 1 THEN '<td><font face="Verdana" size="1">Success</font></td>'
WHEN 3 THEN '<td><font face="Verdana" size="1">Cancelled</font></td>'
WHEN 5 THEN '<td><font face="Verdana" size="1">Unknown</font></td>'
ELSE '<td><font face="Verdana" size="1">Other</font></td>'
END +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), A.category),'') + '</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),'') + '</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),'') +'</font></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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Databases</font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="75%" border="1">
<tr>
<td width="35%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Name</font></b></td>
<td width="23%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">CreatedDate</font></b></td>
<td width="23%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">DB Size(GB)</font></b></td>
<td width="30%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">State</font></b></td>
<td width="50%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">RecoveryModel</font></b></td>
</tr>'
select
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(name, '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + '-' + CONVERT(VARCHAR(4),DATEPART(yy,create_date)) +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(state_desc, '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(recovery_model_desc, '') +'</font></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.database_id
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 > 5
'
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 + '<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Index Fragmentation (> 70%)</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" width="75%" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database Name</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Object Name</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Index Name</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Avg Frag (%)</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Page Count</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Type</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">ActionRequired</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr style="color:#F00"><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), DatabaseName), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), ObjectName), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), indexName), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), avg_fragmentation_percent), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), page_count), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), IndexType), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Action_Required), '') +'</font></td></tr>'
FROM
#db_frag
SELECT @TableHTML = @TableHTML + '</table>'
--=========================index fragmentation ends
-- Code for SQL Server Database Backup Stats
SELECT
@TableHTML = @TableHTML +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Backup Stats</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="75%" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" width="91" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Date</font></th>
<th align="left" width="105" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database</font></th>
<th align="left" width="165" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">File Name</font></th>
<th align="left" width="75" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Type</font></th>
<th align="left" width="165" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Start Time</font></th>
<th align="left" width="165" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">End Time</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Size(GB)</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + 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)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), MST.database_name), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), MST.name), '') +'</font></td>' +
CASE Type
WHEN 'D' THEN '<td><font face="Verdana" size="1">' + 'Full' +'</font></td>'
WHEN 'I' THEN '<td><font face="Verdana" size="1">' + 'Differential' +'</font></td>'
WHEN 'L' THEN '<td><font face="Verdana" size="1">' + 'Log' +'</font></td>'
WHEN 'F' THEN '<td><font face="Verdana" size="1">' + 'File or Filegroup' +'</font></td>'
WHEN 'G' THEN '<td><font face="Verdana" size="1">' + 'File Differential' +'</font></td>'
WHEN 'P' THEN '<td><font face="Verdana" size="1">' + 'Partial' +'</font></td>'
WHEN 'Q' THEN '<td><font face="Verdana" size="1">' + 'Partial Differential' +'</font></td>'
ELSE '<td><font face="Verdana" size="1">' + 'Unknown' +'</font></td>'
END +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), '') +'</font></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 +
--'<p style="margin-top: 1; margin-bottom: 0"> </p>
--<font face="Verdana" size="4">Physical Backup Files</font>
--<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">
--<font face="Verdana" size="1" color="#FFFFFF">Physical Files</font></th>
--</tr>'
--SELECT
--@TableHTML = @TableHTML + '<tr>' +
--CASE SUBSTRING(files, 1, 5)
--WHEN 'PATH:' THEN '<td bgcolor = "#D7D7D7"><b><font face="Verdana" size="1">' + files + '</font><b></td>'
--ELSE
--'<td><font face="Verdana" size="1">' + files + '</font></td>'
--END +
--'</tr></table>'
--FROM
--#dirpaths
--=========================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 +
'<br><font face="Verdana" size="4">Database Mirroring Status</font>
</table><table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0"
width="75%" border="1">
<tr>
<td width="15%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Database Name</font></b></td>
<td width="15%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Database ID</font></b></td>
<td width="30%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Mirror State</font></b></td>
<td width="70%" bgColor="#000080" height="15"><b>
<font face="Verdana" size="1" color="#FFFFFF">Mirror Partner Name </font></b></td>
</tr>
<p style="margin-top: 1; margin-bottom: 0"> </p>
'
SELECT
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100),
ms.name), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100),
ms.mdbid), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100),
ms.status), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100),
ms.partnername), '') +
+'</font></td></tr>'
FROM
#mirror_status ms
SELECT @TableHTML = @TableHTML + '</table>'
end
else
begin
SELECT
@TableHTML = @TableHTML +
'<br><p><font face="Verdana" size="4">Database Mirroring Status</font> (Mirroring not configured)</p>'
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,
when 2 then ''latency''
case warning
when 1 then ''expiration''
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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Replication Statistics</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="75%" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">AgentName</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">PublicationType</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">AgentType</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Status</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Warning</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last_Dist_Sync</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Retention</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">avg_latency</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">average_runspeedPerf</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td><font face="Verdana" size="1">' + isnull(cast(agent_name as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(PublicationType as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(AgentType as varchar(500)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast([Status] as varchar(50)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast([Warning] as varchar(200)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_distsync as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast([retention] as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(avg_latency as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(average_runspeedPerf as varchar(100)),'') +'</font></td>' +
'</tr>'
FROM #replication_status
SELECT
@TableHTML = @TableHTML + N'</table>'
end
else
begin
SELECT
@TableHTML = @TableHTML +
'<br><p><font face="Verdana" size="4">Replication Status</font> (Replication not configured / No publications)</p>'
end
--============replication status ends
-----------------------------
-- ======== DB Log Shipping Monitor
if exists (select * from msdb..log_shipping_monitor_primary)
begin
SELECT
@TableHTML = @TableHTML +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Log Shipping Stats</font><br />
<font face="Verdana" size="2">Local Primaries</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" width="75%" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Instance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">BackupThreshold</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastBackup</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastBackupFile</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_database as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(backup_threshold as varchar(10)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_backup_date as varchar(50)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_backup_file as varchar(300)),'') +'</font></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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="2">Local Secondaries</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" width="75%" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">PrimaryInstance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SecondaryInstance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">RestoreThreshold</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastRestore</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastRestoredFile</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(secondary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_database as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(restore_threshold as varchar(10)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_restored_date as varchar(50)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_restored_file as varchar(300)),'') +'</font></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 +
'<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="2">Remote Secondaries</font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" width="75%" cellPadding="0" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">PrimaryInstance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SecondaryInstance</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">RestoreThreshold</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastRestore</font></th>
<th align="left" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LastRestoredFile</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td><font face="Verdana" size="1">' + isnull(cast(primary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(secondary_server as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(secondary_database as varchar(100)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(restore_threshold as varchar(10)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_restored_date as varchar(50)),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + isnull(cast(last_restored_file as varchar(300)),'') +'</font></td>' +
'</tr>'
FROM #remoteLSStats
SELECT
@TableHTML = @TableHTML + N'</table>'
end
-- ========== DB Log shipping monitor ends
SELECT
@TableHTML = @TableHTML +
'<p style="margin-top: 0; margin-bottom: 0"> </p>
<hr color="#000000" size="1">
<!--<p><font face="Verdana" size="2"><b>Server Owner:</b> '+@owner+'</font></p> -->
<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Thanks
and Regards,</font></p>
<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">DB
Support Team</font></p>
<p> </p>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients=@Recepients,
@subject = @strSubject,
@body = @TableHTML,
@body_format = 'HTML' ;
--print @TableHTML
SET NOCOUNT OFF
SQLHub 