Ashok Kumar SQL Development and DBA Adminstrations Techi Blog

Sunday, 9 April 2017

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

T SQL Script to find Total Datbase/DB size in MB and GB and Total Database/DB Count

T SQL Script to find Total DB size in MB and GB and Total Database Count

Create Table #DBDETAILS(Name Char(100),db_size char(30),owner char(100),dbid int,created char(30),
status nvarchar(max),comptlevel int)

Insert into #DBDETAILS
Exec master.dbo.sp_helpdb

Create Table #DBDETAILScount(Name Char(100),db_int float)

insert into #DBDETAILScount

Select name,left(db_size,10) db_int from #DBDETAILS

Create Table #DBCOUNTGB(dbsum float,[dbcount] int)

insert into #DBCOUNTGB

Select sum(db_int)[dbsum],count(name) [dbcount]
from #DBDETAILScount

select @@servername as ServerName,Round([dbsum],0) [Total DB Size in MB],Round([dbsum]/1024,0) as [Total DB Size in GB],dbcount as [db count] from #DBCOUNTGB

drop table #DBDETAILS

drop table #DBDETAILScount

drop table #DBCOUNTGB

Tuesday, 21 March 2017

Database Mail The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required in ASP.Net Application.

The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required in ASP.Net Application.

Ref:

https://www.aspsnippets.com/Articles/GMAIL-Error-The-SMTP-server-requires-a-secure-connection-or-the-client-was-not-authenticated.aspx

when you configured database mail but still you are getting error

solution:

you will get mail from your gmail account and click on below link to activate it

Kindly click above link and turn on

Now sending test mail

success

now we got mail from dbmail

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]

https://support.microsoft.com/en-in/help/956013/error-message-when-you-open-sql-server-configuration-manager-in-sql-server-cannot-connect-to-wmi-provider.-you-do-not-have-permission-or-the-server-is-unreachable

I have installed SQL 2012 on my machine but when i tried to open configuration manager i got
error as above

to work around see this page

https://support.microsoft.com/en-in/help/956013/error-message-when-you-open-sql-server-configuration-manager-in-sql-server-cannot-connect-to-wmi-provider.-you-do-not-have-permission-or-the-server-is-unreachable

This problem also occurs if you uninstall the 32-bit instance, and you then open SQL Server Configuration Manager.

Cause

This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

Workaround

To work around this problem, open a command prompt, type the following command, and then press ENTER:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"

Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

The value of number depends on the version of SQL Server:nnn

Microsoft SQL Server 2012	110
Microsoft SQL Server 2008 R2	100
Microsoft SQL Server 2008	100
Microsoft SQL Server 2005	90

After you run the Mofcomp tool, restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation.

after run this query in command prompt now am open configuration manager it opens with no issue

Thanks for reading

How many CPU cores is SQL Server using?

How many CPU cores is SQL Server using?

CPU count:

select cpu_count from sys.dm_os_sys_info

How many CPU cores is SQL Server using:

select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'

Saturday, 18 February 2017

How to set Secondary File NDF file growth and restrict file size growth in MDF file in SQL Server

New File growth effect Primary MDF and Secondary NDF Added in SQL Server

s p_h e l p db db info

initially it was only 6 MB

Added some sample records to table on test db growth table

after added records you can see growth only on M D F file only

Now we added secondary file

even if you decide to u n select on enable auto growth option on properties of db

it will give effect on auto growth as none

now we check s p_help db

now we insert sample data

it will give error as we disabled auto growth it will not allow data to insert as show error

we disabled auto growth so it will not allow data to insert see the error

So we should need to enable Auto growth on M D F then we are going to set auto growth option on
N D F then it will work as see below

s p_h e l p db of M D F and L D F info as see below

now we are going to alter database to new location as see below

take database offline then move the M D F file to new location as see below

Now move the database log file to new location as see below

after bring online database shows as new location F drive as see below

Now we insert some sample data as see below

Now we new insert data take effect on M D F only but cant insert data on LDF see below

solution:

We should need to limit DB Growth in M D F file as see below

now we see help db info

we should need to change it secondary on clustered index /index on table otherwise table should increase on primary only

after change we insert 10000 Thousand records to table as see below

you can see NDF now growing but MDF is not growing itself

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

T SQL Script to find Total Datbase/DB size in MB and GB and Total Database/DB Count

Tuesday, 21 March 2017

Database Mail The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required in ASP.Net Application.

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

Cause

Workaround

How many CPU cores is SQL Server using?

Saturday, 18 February 2017

How to set Secondary File NDF file growth and restrict file size growth in MDF file in SQL Server

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?