【SQLServer 】SQLServer健康报告
2022-09-22 11:02 abce 阅读(208) 评论(0) 编辑 收藏 举报使用：
1.创建存储过程：usp_SQLhealthcheck_report
2.执行存储过程 execute usp_SQLhealthcheck_report
3.输入mail地址(可选项)
4.保存结果
输出格式：
HTML(支持将html以邮件附件的方式发送)
原文地址：http://udayarumilli.com/sql-server-health-check-html-report/
/****************************************************************************/
/*************** SQL SERVER HEALTH CHECK REPORT - HTML **********************/
--Date : 08-MAY-2020
--Author : UDAY ARUMILLI
--Team : WWW.udayarumilli.com
--Version : 6.0
--Permissions : VIEW SERVER STATE, CREATE TEMP TABLE
-- EXEC TIME : <30 Min - Depends on data volume
/***************************** Report Details *******************************/
-- Tested: SQL Server 2008 R2, 2012, 2014, 2016 and 2017
-- Report Type: HTML Report Delivers to Output Window / Mailbox
-- Parameters: DBMail Profile Name *, Email ID *, Server Name (Optional);
---- Server Details
---- Instance Information
---- Disk Space Usage
---- Tempdb Usage
---- CPU Usage
---- Memory Usage
---- Performance Counter Data
---- Missing Backup Report
---- Database Connections
---- Database Log Space Usage
---- SQL Agent Job Status
---- Blocking Process Information
---- Long Running Transactions
---- Top 20 Table Size
---- Index Analysis
---- SQL ErrorLog
/****************************************************************************/
/****************************************************************************/
IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_SQLhealthcheck_report]') AND [TYPE] = 'P')
BEGIN
DROP PROCEDURE [dbo].[usp_SQLhealthcheck_report];
END
GO
CREATE PROCEDURE [dbo].[usp_SQLhealthcheck_report](
@MailProfile NVARCHAR(200) = NULL,
@MailID NVARCHAR(2000) = NULL,
@Server VARCHAR(100) = NULL)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;
DECLARE @ServerName VARCHAR(100);
SET @ServerName = ISNULL(@Server,@@SERVERNAME);
/*************************************************************/
/****************** Server Reboot Details ********************/
/*************************************************************/
CREATE TABLE #RebootDetails
(
LastRecycle datetime,
CurrentDate datetime,
UpTimeInDays varchar(100)
)
Insert into #RebootDetails
SELECT sqlserver_start_time 'Last Recycle',GetDate() 'Current Date', DATEDIFF(DD, sqlserver_start_time,GETDATE())'Up Time in Days'
FROM sys.dm_os_sys_info;
/*************************************************************/
/****************** Current Blocking Details *****************/
/*************************************************************/
CREATE TABLE #BlkProcesses
(
spid varchar(5),
Blkspid varchar(5),
PrgName varchar(100),
LoginName varchar(100),
ObjName varchar(100),
Query varchar(255)
)
insert into #BlkProcesses
SELECT s.spid, BlockingSPID = s.blocked, substring(s.program_name,1,99), SUBSTRING(s.loginame,1,99),
ObjectName = substring( OBJECT_NAME(objectid, s.dbid),1,99), Definition = CAST(text AS VARCHAR(255))
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50 AND s.blocked > 0
/*************************************************************/
/****************** Errors audit for last 4 Days *************/
/*************************************************************/
CREATE TABLE #ErrorLogInfo_all
(
LogDate datetime,
processinfo varchar(200),
LogInfo varchar(1000)
)
CREATE TABLE #ErrorLogInfo
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
LogDate varchar(100),
LogInfo varchar(2000)
)
DECLARE @A VARCHAR(10), @B VARCHAR(10);
SELECT @A = CONVERT(VARCHAR(20),GETDATE()-1,112);
SELECT @B = CONVERT(VARCHAR(20),GETDATE()+1,112);
INSERT INTO #ErrorLogInfo_all
EXEC XP_READERRORLOG 0, 1,N'Login', N'Failed', @A,@B,'DESC';
INSERT INTO #ErrorLogInfo (LogDate,LogInfo)
select DISTINCT CONVERT(VARCHAR(20),GETDATE()+1,111) "LogDate",LogInfo from #ErrorLogInfo_all;
/***********************************************************/
/************* Windows Disk Space Details ******************/
/***********************************************************/
CREATE TABLE #FreeSpace (DName CHAR(1), Free_MB BIGINT, Free_GB DECIMAL(16,2))
INSERT INTO #FreeSpace (DName,Free_MB) EXEC XP_FIXEDDRIVES;
UPDATE #FreeSpace SET Free_GB = CAST(Free_MB / 1024.00 AS DECIMAL(16,2));
/*************************************************************/
/************* SQL Server CPU Usage Details ******************/
/*************************************************************/
Create table #CPU(
servername varchar(100),
EventTime2 datetime,
SQLProcessUtilization varchar(50),
SystemIdle varchar(50),
OtherProcessUtilization varchar(50),
load_date datetime
)
DECLARE @ts BIGINT; DECLARE @lastNmin TINYINT;
SET @lastNmin = 240;
SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info);
insert into #CPU
SELECT TOP 10 * FROM (
SELECT TOP(@lastNmin)
@ServerName AS 'ServerName',
DATEADD(ms,-1 *(@ts - [timestamp]),GETDATE())AS [Event_Time],
SQLProcessUtilization AS [SQLServer_CPU_Utilization],
SystemIdle AS [System_Idle_Process],
100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],
GETDATE() AS 'LoadDate'
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'%%')AS x )AS y
ORDER BY SystemIdle ASC) d
/*************************************************************/
/************* SQL Server Memory Usage Details ***************/
/*************************************************************/
CREATE TABLE #Memory_BPool (
BPool_Committed_MB VARCHAR(50),
BPool_Commit_Tgt_MB VARCHAR(50),
BPool_Visible_MB VARCHAR(50));
/****
-- SQL server 2008 / 2008 R2
INSERT INTO #Memory_BPool
SELECT
(bpool_committed*8)/1024.0 as BPool_Committed_MB,
(bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,
(bpool_visible*8)/1024.0 as BPool_Visible_MB
FROM sys.dm_os_sys_info;
****/
-- SQL server 2012 / 2014 / 2016
INSERT INTO #Memory_BPool
SELECT
(committed_kb)/1024.0 as BPool_Committed_MB,
(committed_target_kb)/1024.0 as BPool_Commit_Tgt_MB,
(visible_target_kb)/1024.0 as BPool_Visible_MB
FROM sys.dm_os_sys_info;
CREATE TABLE #Memory_sys (
total_physical_memory_mb VARCHAR(50),
available_physical_memory_mb VARCHAR(50),
total_page_file_mb VARCHAR(50),
available_page_file_mb VARCHAR(50),
Percentage_Used VARCHAR(50),
system_memory_state_desc VARCHAR(50));
INSERT INTO #Memory_sys
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,
100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3)))
AS 'Percentage_Used',
system_memory_state_desc
from sys.dm_os_sys_memory;
CREATE TABLE #Memory_process(
physical_memory_in_use_GB VARCHAR(50),
locked_page_allocations_GB VARCHAR(50),
virtual_address_space_committed_GB VARCHAR(50),
available_commit_limit_GB VARCHAR(50),
page_fault_count VARCHAR(50))
INSERT INTO #Memory_process
select
physical_memory_in_use_kb/1048576.0 AS 'Physical_Memory_In_Use(GB)',
locked_page_allocations_kb/1048576.0 AS 'Locked_Page_Allocations(GB)',
virtual_address_space_committed_kb/1048576.0 AS 'Virtual_Address_Space_Committed(GB)',
available_commit_limit_kb/1048576.0 AS 'Available_Commit_Limit(GB)',
page_fault_count as 'Page_Fault_Count'
from sys.dm_os_process_memory;
CREATE TABLE #Memory(
ID INT IDENTITY NOT NULL,
Parameter VARCHAR(200),
Value VARCHAR(100));
INSERT INTO #Memory
SELECT 'BPool_Committed_MB',BPool_Committed_MB FROM #Memory_BPool
UNION
SELECT 'BPool_Commit_Tgt_MB', BPool_Commit_Tgt_MB FROM #Memory_BPool
UNION
SELECT 'BPool_Visible_MB', BPool_Visible_MB FROM #Memory_BPool
UNION
SELECT 'Total_Physical_Memory_MB',total_physical_memory_mb FROM #Memory_sys
UNION
SELECT 'Available_Physical_Memory_MB',available_physical_memory_mb FROM #Memory_sys
UNION
SELECT 'Percentage_Used',Percentage_Used FROM #Memory_sys
UNION
SELECT 'System_memory_state_desc',system_memory_state_desc FROM #Memory_sys
UNION
SELECT 'Total_page_file_mb',total_page_file_mb FROM #Memory_sys
UNION
SELECT 'Available_page_file_mb',available_page_file_mb FROM #Memory_sys
UNION
SELECT 'Physical_memory_in_use_GB',physical_memory_in_use_GB FROM #Memory_process
UNION
SELECT 'Locked_page_allocations_GB',locked_page_allocations_GB FROM #Memory_process
UNION
SELECT 'Virtual_Address_Space_Committed_GB',virtual_address_space_committed_GB FROM #Memory_process
UNION
SELECT 'Available_Commit_Limit_GB',available_commit_limit_GB FROM #Memory_process
UNION
SELECT 'Page_Fault_Count',page_fault_count FROM #Memory_process;
/******************************************************************/
/*************** Performance Counter Details **********************/
/******************************************************************/
CREATE TABLE #PerfCntr_Data(
ID INT IDENTITY NOT NULL,
Parameter VARCHAR(300),
Value VARCHAR(100));
-- Get size of SQL Server Page in bytes
DECLARE @pg_size INT, @Instancename varchar(50)
SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
-- Extract perfmon counters to a temporary table
IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters
SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters;
-- Get SQL Server instance name as it require for capturing Buffer Cache hit Ratio
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name])))
FROM #perfmon_counters
WHERE counter_name = 'Buffer cache hit ratio';
INSERT INTO #PerfCntr_Data
SELECT CONVERT(VARCHAR(300),Cntr) AS Parameter, CONVERT(VARCHAR(100),Value) AS Value
FROM
(
SELECT 'Page Life Expectency in seconds' as Cntr,
cntr_value AS Value
FROM #perfmon_counters
WHERE object_name=@Instancename+'Buffer Manager'
and counter_name = 'Page life expectancy'
UNION ALL
SELECT 'BufferCache HitRatio' as Cntr,
(a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS Value
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 = @Instancename+'Buffer Manager') b ON
a.OBJECT_NAME = b.OBJECT_NAME WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = @Instancename+'Buffer Manager'
UNION ALL
SELECT 'Total Server Memory (GB)' as Cntr,
(cntr_value/1048576.0) AS Value
FROM #perfmon_counters
WHERE counter_name = 'Total Server Memory (KB)'
UNION ALL
SELECT 'Target Server Memory (GB)',
(cntr_value/1048576.0)
FROM #perfmon_counters
WHERE counter_name = 'Target Server Memory (KB)'
UNION ALL
SELECT 'Connection Memory (MB)',
(cntr_value/1024.0)
FROM #perfmon_counters
WHERE counter_name = 'Connection Memory (KB)'
UNION ALL
SELECT 'Lock Memory (MB)',
(cntr_value/1024.0)
FROM #perfmon_counters
WHERE counter_name = 'Lock Memory (KB)'
UNION ALL
SELECT 'SQL Cache Memory (MB)',
(cntr_value/1024.0)
FROM #perfmon_counters
WHERE counter_name = 'SQL Cache Memory (KB)'
UNION ALL
SELECT 'Optimizer Memory (MB)',
(cntr_value/1024.0)
FROM #perfmon_counters
WHERE counter_name = 'Optimizer Memory (KB) '
UNION ALL
SELECT 'Granted Workspace Memory (MB)',
(cntr_value/1024.0)
FROM #perfmon_counters
WHERE counter_name = 'Granted Workspace Memory (KB) '
UNION ALL
SELECT 'Cursor memory usage (MB)',
(cntr_value/1024.0)
FROM #perfmon_counters
WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total'
UNION ALL
SELECT 'Total pages Size (MB)',
(cntr_value*@pg_size)/1048576.0
FROM #perfmon_counters
WHERE object_name= @Instancename+'Buffer Manager'
and counter_name = 'Total pages'
UNION ALL
SELECT 'Database pages (MB)',
(cntr_value*@pg_size)/1048576.0
FROM #perfmon_counters
WHERE object_name = @Instancename+'Buffer Manager' and counter_name = 'Database pages'
UNION ALL
SELECT 'Free pages (MB)',
(cntr_value*@pg_size)/1048576.0
FROM #perfmon_counters
WHERE object_name = @Instancename+'Buffer Manager'
and counter_name = 'Free pages'
UNION ALL
SELECT 'Reserved pages (MB)',
(cntr_value*@pg_size)/1048576.0
FROM #perfmon_counters
WHERE object_name=@Instancename+'Buffer Manager'
and counter_name = 'Reserved pages'
UNION ALL
SELECT 'Stolen pages (MB)',
(cntr_value*@pg_size)/1048576.0
FROM #perfmon_counters
WHERE object_name=@Instancename+'Buffer Manager'
and counter_name = 'Stolen pages'
UNION ALL
SELECT 'Cache Pages (MB)',
(cntr_value*@pg_size)/1048576.0
FROM #perfmon_counters
WHERE object_name=@Instancename+'Plan Cache'
and counter_name = 'Cache Pages' and instance_name = '_Total'
UNION ALL
SELECT 'Free list stalls/sec',
cntr_value
FROM #perfmon_counters
WHERE object_name=@Instancename+'Buffer Manager'
and counter_name = 'Free list stalls/sec'
UNION ALL
SELECT 'Checkpoint pages/sec',
cntr_value
FROM #perfmon_counters
WHERE object_name=@Instancename+'Buffer Manager'
and counter_name = 'Checkpoint pages/sec'
UNION ALL
SELECT 'Lazy writes/sec',
cntr_value
FROM #perfmon_counters
WHERE object_name=@Instancename+'Buffer Manager'
and counter_name = 'Lazy writes/sec'
UNION ALL
SELECT 'Memory Grants Pending',
cntr_value
FROM #perfmon_counters
WHERE object_name=@Instancename+'Memory Manager'
and counter_name = 'Memory Grants Pending'
UNION ALL
SELECT 'Memory Grants Outstanding',
cntr_value
FROM #perfmon_counters
WHERE object_name=@Instancename+'Memory Manager'
and counter_name = 'Memory Grants Outstanding'
UNION ALL
SELECT 'Process_Physical_Memory_Low',
process_physical_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK)
UNION ALL
SELECT 'Process_Virtual_Memory_Low',
process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK)
UNION ALL
SELECT 'Max_Server_Memory (MB)' ,
[value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)'
UNION ALL
SELECT 'Min_Server_Memory (MB)' ,
[value_in_use]
FROM sys.configurations
WHERE [name] = 'min server memory (MB)') AS P;
/******************************************************************/
/*************** Database Backup Report ***************************/
/******************************************************************/
CREATE TABLE #Backup_Report(
Database_Name VARCHAR(300),
Last_Backup_Date VARCHAR(50));
INSERT INTO #Backup_Report
--Databases with data backup over 48 hours old
SELECT Database_Name, last_db_backup_date AS Last_Backup_Date FROM (
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(DD, -7, GETDATE()))
UNION
--Databases without any backup history
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
sd.NAME AS database_name,
NULL AS [Last Data Backup Date],
9999 AS [Backup Age (Hours)]
FROM master.dbo.sysdatabases sd
LEFT JOIN msdb.dbo.backupset bs
ON sd.name = bs.database_name
WHERE bs.database_name IS NULL AND sd.name <> 'tempdb' ) AS B
ORDER BY Database_Name;
/*************************************************************/
/****************** Connection Details ***********************/
/*************************************************************/
-- Number of connection on the instance grouped by hostnames
Create table #ConnInfo(
Hostname varchar(100),
NumberOfconn varchar(10)
)
insert into #ConnInfo
SELECT Case when len(hostname)=0 Then 'Internal Process' Else hostname END,count(*)NumberOfconnections
FROM sys.sysprocesses
GROUP BY hostname
/*************************************************************/
/************** Currently Running Jobs Info ******************/
/*************************************************************/
Create table #JobInfo(
spid varchar(10),
lastwaittype varchar(100),
dbname varchar(100),
login_time varchar(100),
status varchar(100),
opentran varchar(100),
hostname varchar(100),
JobName varchar(100),
command nvarchar(2000),
domain varchar(100),
loginname varchar(100)
)
insert into #JobInfo
SELECT distinct p.spid,p.lastwaittype,DB_NAME(p.dbid),p.login_time,p.status,p.open_tran,p.hostname,J.name,
p.cmd,p.nt_domain,p.loginame
FROM master..sysprocesses p
INNER JOIN msdb..sysjobs j ON
substring(left(j.job_id,8),7,2) + substring(left(j.job_id,8),5,2) + substring(left(j.job_id,8),3,2) + substring(left(j.job_id,8),1,2) = substring(p.program_name, 32, 8)
Inner join msdb..sysjobactivity sj on j.job_id=sj.job_id
WHERE program_name like'SQLAgent - TSQL JobStep (Job %' and sj.stop_execution_date is null
/*************************************************************/
/****************** Tempdb File Info *************************/
/*************************************************************/
-- tempdb file usage
Create table #tempdbfileusage(
servername varchar(100),
databasename varchar(100),
filename varchar(100),
physicalName varchar(100),
filesizeMB varchar(100),
availableSpaceMB varchar(100),
percentfull varchar(100)
)
DECLARE @TEMPDBSQL NVARCHAR(4000);
SET @TEMPDBSQL = ' USE Tempdb;
SELECT CONVERT(VARCHAR(100), @@SERVERNAME) AS [server_name]
,db.name AS [database_name]
,mf.[name] AS [file_logical_name]
,mf.[filename] AS[file_physical_name]
,convert(FLOAT, mf.[size]/128) AS [file_size_mb]
,convert(FLOAT, (mf.[size]/128 - (CAST(FILEPROPERTY(mf.[name], ''SpaceUsed'') AS int)/128))) as [available_space_mb]
,convert(DECIMAL(38,2), (CAST(FILEPROPERTY(mf.[name], ''SpaceUsed'') AS int)/128.0)/(mf.[size]/128.0))*100 as [percent_full]
FROM tempdb.dbo.sysfiles mf
JOIN master..sysdatabases db
ON db.dbid = db_id()';
--PRINT @TEMPDBSQL;
insert into #tempdbfileusage
EXEC SP_EXECUTESQL @TEMPDBSQL;
/*************************************************************/
/****************** Database Log Usage ***********************/
/*************************************************************/
CREATE TABLE #LogSpace(
DBName VARCHAR(100),
LogSize VARCHAR(50),
LogSpaceUsed_Percent VARCHAR(100),
LStatus CHAR(1));
INSERT INTO #LogSpace
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;');
/********************************************************************/
/****************** Long Running Transactions ***********************/
/********************************************************************/
CREATE TABLE #OpenTran_Detail(
[SPID] [varchar](20) NULL,
[TranID] [varchar](50) NULL,
[User_Tran] [varchar](5) NOT NULL,
[DBName] [nvarchar](250) NULL,
[Login_Time] [varchar](60) NULL,
[Duration] [varchar](20) NULL,
[Last_Batch] [varchar](200) NULL,
[Status] [nvarchar](50) NULL,
[LoginName] [nvarchar](250) NULL,
[HostName] [nvarchar](250) NULL,
[ProgramName] [nvarchar](250) NULL,
[CMD] [nvarchar](50) NULL,
[SQL] [nvarchar](max) NULL,
[Blocked] [varchar](6) NULL
);
;WITH OpenTRAN AS
(SELECT session_id,transaction_id,is_user_transaction
FROM sys.dm_tran_session_transactions)
INSERT INTO #OpenTran_Detail
SELECT
LTRIM(RTRIM(OT.session_id)) AS 'SPID',
LTRIM(RTRIM(OT.transaction_id)) AS 'TranID',
CASE WHEN OT.is_user_transaction = '1' THEN 'Yes' ELSE 'No' END AS 'User_Tran',
db_name(LTRIM(RTRIM(s.dbid)))DBName,
LTRIM(RTRIM(login_time)) AS 'Login_Time',
DATEDIFF(MINUTE,login_time,GETDATE()) AS 'Duration',
LTRIM(RTRIM(last_batch)) AS 'Last_Batch',
LTRIM(RTRIM(status)) AS 'Status',
LTRIM(RTRIM(loginame)) AS 'LoginName',
LTRIM(RTRIM(hostname)) AS 'HostName',
LTRIM(RTRIM(program_name)) AS 'ProgramName',
LTRIM(RTRIM(cmd)) AS 'CMD',
LTRIM(RTRIM(a.text)) AS 'SQL',
LTRIM(RTRIM(blocked)) AS 'Blocked'
FROM sys.sysprocesses AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle)a
INNER JOIN OpenTRAN AS OT ON OT.session_id = s.spid
WHERE s.spid <> @@spid AND s.dbid>4;
/********************************************************************/
/****************** Top 20 Tables ***********************/
/********************************************************************/
CREATE TABLE #Tab (
[Name] NVARCHAR(128),
[Rows] CHAR(11),
[Reserved] VARCHAR(18),
[Data] VARCHAR(18),
[Index_size] VARCHAR(18),
[Unused] VARCHAR(18));
CREATE TABLE #Table_Counts (
ID INT IDENTITY NOT NULL PRIMARY KEY,
[Table_Name] NVARCHAR(128),
[Row_Count] VARCHAR(100),
[TotalSize(MB)] VARCHAR(18),
[TotalSize(GB)] VARCHAR(18),
[Data(MB)] VARCHAR(18),
[Index_Size(MB)] VARCHAR(18),
[UnUsed(MB)] VARCHAR(18));
--Capture all tables data allocation information
INSERT #Tab
EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' ;
--Alter Rows column datatype to BIGINT to get the result in sorted order
ALTER TABLE #Tab ALTER COLUMN [ROWS] BIGINT ;
-- Get the final result: Remove KB and convert it into MB
INSERT INTO #Table_Counts ([Table_Name],[Row_Count],[TotalSize(MB)],[TotalSize(GB)],[Data(MB)],[Index_Size(MB)],[UnUsed(MB)])
SELECT TOP 20
Name,
[Rows],
CAST(CAST(LTRIM(RTRIM(REPLACE(Reserved,'KB',''))) AS BIGINT)/1024.0 AS DECIMAL(18,2)) AS 'TotalSize MB',
CAST(CAST(LTRIM(RTRIM(REPLACE(Reserved,'KB',''))) AS BIGINT)/(1024.0*1024.0) AS DECIMAL(18,2)) AS 'TotalSize GB',
CAST(CAST(LTRIM(RTRIM(REPLACE(Data,'KB',''))) AS BIGINT)/1024.0 AS DECIMAL(18,2)) AS 'Data MB',
CAST(CAST(LTRIM(RTRIM(REPLACE(Index_Size,'KB',''))) AS BIGINT)/1024.0 AS DECIMAL(18,2)) AS 'Index_Size MB',
CAST(CAST(LTRIM(RTRIM(REPLACE(Unused,'KB',''))) AS BIGINT)/1024.0 AS DECIMAL(18,2)) AS 'Unused MB'
FROM #Tab
ORDER BY CAST(CAST(LTRIM(RTRIM(REPLACE(Reserved,'KB',''))) AS BIGINT)/1024.0 AS DECIMAL(18,2)) DESC;
/********************************************************************/
/****************** Index Fragmentation Details ***********************/
/********************************************************************/
CREATE TABLE #IdxFrag_Detail(
ID INT IDENTITY PRIMARY KEY NOT NULL,
[SCHEMA] NVARCHAR(250),
[TABLE] NVARCHAR(250),
[INDEX] NVARCHAR(250),
[FRAGMENTATION] NVARCHAR(250),
[PAGE_COUNT] NVARCHAR(500),
[STATUS] NVARCHAR(250));
INSERT INTO #IdxFrag_Detail ([SCHEMA],[TABLE],[INDEX],[FRAGMENTATION],[PAGE_COUNT],[STATUS])
SELECT TOP 50
object_schema_name(ips.object_id) AS 'Schema_Name',
object_name (ips.object_id) AS 'Object_Name',
i.name AS 'Index_Name',
ips.avg_fragmentation_in_percent AS 'Avg_Fragmentation%',
ips.page_count AS 'Page_Count',
CASE WHEN (ips.avg_fragmentation_in_percent BETWEEN 5 AND 30) AND ips.page_count > 1000
THEN 'Reorganize'
WHEN ips.avg_fragmentation_in_percent > 30 AND ips.page_count > 1000
THEN 'Rebuild'
ELSE 'Healthy'
END AS 'Index_Status'
FROM sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ips
INNER JOIN sys.indexes i ON i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.index_id > 0
ORDER BY avg_fragmentation_in_percent DESC;
/*************************************************************/
/****************** HTML Preparation *************************/
/*************************************************************/
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),
@Cnt int,
@URL varchar(1000),
@Str varchar(1000),
@NoofCriErrors varchar(3)
-- Variable Assignment
SELECT @Version = @@version
SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))
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('servername'))
SELECT @strSubject = 'Database Server Health Check ('+ CONVERT(VARCHAR(100), @SERVERNAME) + ')'
SET @TableHTML =
'
'
SET @TableHTML = @TableHTML +
'<div><img align="right" src="Images/Logo_Image.jpg" style="height: 50px; width: 50px"/><font face="Verdana" size="4" color="#3399ff"><H2><bold>Database Health Check Report</bold></H2></font></div>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">
<tr>
<td width="39%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
</tr>
<tr>
<td width="39%" height="27"><font face="Verdana" size="2">' + @ServerName +'</font></td>
</tr>
</table>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="2">
<tr>
<td align="Center" width="50%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Version</font></b></td>
<td align="Center" width="17%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Edition</font></b></td>
<td align="Center" width="35%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Service Pack</font></b></td>
<td align="Center" width="60%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Collation</font></b></td>
<td align="Center" width="93%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">LicenseType</font></b></td>
<td align="Center" width="40%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">SingleUser</font></b></td>
<td align="Center" width="93%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Clustered</font></b></td>
</tr>
<tr>
<td align="Center" width="50%" height="27"><font face="Verdana" size="1">'+@version +'</font></td>
<td align="Center" width="17%" height="27"><font face="Verdana" size="1">'+@edition+'</font></td>
<td align="Center" width="18%" height="27"><font face="Verdana" size="1">'+@SP+'</font></td>
<td align="Center" width="17%" height="27"><font face="Verdana" size="1">'+@ServerCollation+'</font></td>
<td align="Center" width="25%" height="27"><font face="Verdana" size="1">'+@LicenseType+'</font></td>
<td align="Center" width="25%" height="27"><font face="Verdana" size="1">'+@SingleUser+'</font></td>
<td align="Center" width="93%" height="27"><font face="Verdana" size="1">'+@isclustered+'</font></td>
</tr>'
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Instance last Recycled</bold></H3></font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="47%" bgColor="#ffffff" borderColorLight="#000000" border="2">
<tr>
<th align="Center" width="50" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last Recycle</font></th>
<th align="Center" width="50" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Current DateTime</font></th>
<th align="Center" width="50" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">UpTimeInDays</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), LastRecycle ), '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), CurrentDate ), '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), UpTimeInDays ), '') +'</font></td>' +
'</tr>'
FROM
#RebootDetails
/***** Free Disk Space Report ****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Disk Space Report</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="47%" border="2">
<tr>
<th align="Center" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Drive_Name</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Free_Space_GB</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), DName ), '') +'</font></td>' +
CASE WHEN Free_GB < 30 THEN
'<td align="Center"><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), Free_GB), '') +'</font></td>'
ELSE
'<td align="Center"><font face="Verdana" size="1" color="#40C211"><b>' + ISNULL(CONVERT(VARCHAR(100), Free_GB), '') +'</font></td>'
END +
'</tr>'
FROM
#FreeSpace
/**** Tempdb File Usage *****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Tempdb File Usage</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="2">
<tr>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database Name</font></th>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">File Name</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Physical Name</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">FileSize MB</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Available MB</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Percent_full </font></th>
</tr>'
select
@TableHTML = @TableHTML +
'<tr>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(databasename, '') + '</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(FileName, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(physicalName, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(filesizeMB, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(availableSpaceMB, '') +'</font></td>' +
CASE WHEN CONVERT(DECIMAL(10,3),percentfull) >80.00 THEN
'<td align="Center"><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(percentfull, '') +'</b></font></td></tr>'
ELSE
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(percentfull, '') +'</font></td></tr>' END
from
#tempdbfileusage
/**** CPU Usage *****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>CPU Usage</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="80%" border="2">
<tr>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">System Time</font></th>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SQLProcessUtilization</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SystemIdle</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">OtherProcessUtilization</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">load DateTime</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), EventTime2 ), '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), SQLProcessUtilization ), '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), SystemIdle ), '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), OtherProcessUtilization ), '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), load_date ), '') +'</font></td> </tr>'
FROM
#CPU ORDER BY EventTime2;
/***** Memory Usage ****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Memory Usage</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="47%" border="2">
<tr>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Parameter</font></th>
<th align="left" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Value</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(200), Parameter ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Value ), '') +'</font></td>' +
'</tr>'
FROM
#Memory ORDER BY ID;
/***** Performance Counter Values ****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Performance Counter Data</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="47%" border="2">
<tr>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Performance_Counter</font></th>
<th align="left" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Value</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(300), Parameter ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Value ), '') +'</font></td>' +
'</tr>'
FROM
#PerfCntr_Data ORDER BY ID;
/***** Database Backup Report ****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Missing Backup Report</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="47%" border="2">
<tr>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database_Name</font></th>
<th align="left" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last_Backup_Date</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Database_Name ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Last_Backup_Date), '') +'</font></td>' +
'</tr>'
FROM
#Backup_Report
/****** Connection Information *****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Total Number of Database Connections</bold></H3></font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="47%" bgColor="#ffffff" borderColorLight="#000000" border="2">
<tr>
<th align="Center" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Host Name</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Total</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Hostname ), '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), NumberOfconn ), '') +'</font></td>' +
'</tr>'
FROM
#ConnInfo
/***** Log Space Usage ****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Database Log Space Usage</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="47%" border="2">
<tr>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">DatabaseName</font></th>
<th align="left" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Log_Space_Used</font></th>
<th align="left" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Log_Usage_%</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), DBName ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), LogSize ), '') +'</font></td>' +
CASE WHEN CONVERT(DECIMAL(10,3),LogSpaceUsed_Percent) >80.00 THEN
'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), LogSpaceUsed_Percent ), '') +'</b></font></td>'
ELSE
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), LogSpaceUsed_Percent ), '') +'</font></td>'
END +
'</tr>'
FROM
#LogSpace
/******** Job Info *******/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>SQL Agent Job Status</bold></H3></font>' +
'<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="2">
<tr>
<th align="left" width="430" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">spid</font></th>
<th align="left" width="70" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">latwaittype</font></th>
<th align="left" width="85" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">dbname</font></th>
<th align="left" width="183" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Process Login time</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">status</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">opentran</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">hostname</font></th>
<th align="left" width="146" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">JobName</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">command</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">domain</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LoginName</font></th>
</tr>'
SELECT
@TableHTML = ISNULL(CONVERT(VARCHAR(MAX), @TableHTML), 'No Job Running') + '<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), spid), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), lastwaittype),'') + '</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), dbname),'') + '</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), login_time),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), status),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), opentran),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), hostname),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(500), JobName),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(200), command),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), domain),'') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50),loginname ),'') + '</font></td></tr>'
FROM
#JobInfo
/****** Blocking Information ****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Blocking Process Information</bold></H3></font>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="2">
<tr>
<th align="Center" width="50" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">ServerName</font></th>
<th align="Center" width="50" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SpID</font></th>
<th align="Center" width="50" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">BlockingSPID</font></th> <th align="Center" width="50" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">ProgramName</font></th>
<th align="Center" width="50" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LoginName</font></th>
<th align="Center" width="40" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">ObjName</font></th>
<th align="left" width="150" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Query</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), @SERVERNAME ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), spid ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Blkspid ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), PrgName ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), LoginName ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), ObjName ), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Query ), '') +'</font></td>' +
'</tr>'
FROM
#BlkProcesses
ORDER BY spid
/**** Long running Transactions*****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Long Running Transactions</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="2">
<tr>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SPID</font></th>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">TranID</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">User_Tran</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">DB_Name</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Login_Time</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Duration</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Last_Batch</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Status</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">LoginName</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Host_Name</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">PrgName</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">CMD</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SQL</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Blocked </font></th>
</tr>'
select
@TableHTML = @TableHTML +
'<tr>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(SPID, '') + '</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(TranID, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(User_Tran, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(DBName, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(Login_Time, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(Duration, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(Last_Batch, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([Status], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(LoginName, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(HostName, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(ProgramName, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(CMD, '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([SQL], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL(Blocked, '') +'</font></td></tr>'
from
#OpenTran_Detail
/**** Top 20 Tables in Database*****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Top 20 Huge Tables</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="2">
<tr>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Table_Name</font></th>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Row_Count</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">TotalSize(MB)</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">TotalSize(GB)</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Data(MB)</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Index_Size(MB)</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">UnUsed(MB)</font></th>
</tr>'
select
@TableHTML = @TableHTML +
'<tr>' +
'<td align="LEFT"><font face="Verdana" size="1">' + ISNULL([Table_Name], '') + '</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([Row_Count], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([TotalSize(MB)], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([TotalSize(GB)], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([Data(MB)], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([Index_Size(MB)], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([UnUsed(MB)], '') +'</font></td></tr>'
from
#Table_Counts ORDER BY ID;
/**** INDEX ANALYSIS*****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>Index Analysis</bold></H3></font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="2">
<tr>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">SCHEMA</font></th>
<th align="Center" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">TABLE</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">INDEX</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">FRAGMENTATION%</font></th>
<th align="Center" width="250" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">PAGE_COUNT</font></th>
<th align="Center" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Status</font></th>
</tr>'
select
@TableHTML = @TableHTML +
'<tr>' +
'<td align="LEFT"><font face="Verdana" size="1">' + ISNULL([SCHEMA], '') + '</font></td>' +
'<td align="LEFT"><font face="Verdana" size="1">' + ISNULL([TABLE], '') +'</font></td>' +
'<td align="LEFT"><font face="Verdana" size="1">' + ISNULL([INDEX], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([FRAGMENTATION], '') +'</font></td>' +
'<td align="Center"><font face="Verdana" size="1">' + ISNULL([PAGE_COUNT], '') +'</font></td>' +
CASE WHEN [STATUS] = 'HEALTHY' THEN
'<td align="Center"><font face="Verdana" size="1" color="#1ACC25"><b>' + ISNULL([STATUS], '') +'</b></font></td>'
ELSE
'<td align="Center"><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL([STATUS], '') +'</b></font></td>'
END +
'</tr>'
from
#IdxFrag_Detail ORDER BY ID;
/*** Error Log *****/
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 0; margin-bottom: 0"> </p>
<font face="Verdana" size="4" color="#3399ff"><H3><bold>SQL ErrorLog</bold></H3></font>' +
'<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="47%" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
<td width="20%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Check for Suspecious Errors - If Any</font></b></td>
</tr>
</table>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="47%" bgColor="#ffffff" borderColorLight="#000000" border="2">
<tr>
<td width="20%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Error Log DateTime</font></b></td>
<td width="80%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Error Message</font></b></td>
</tr>';
SELECT
@TableHTML = @TableHTML + '<tr>
<td width="20%" height="27"><font face="Verdana" size="1">'+ ISNULL(CONVERT(VARCHAR(100),LogDate ),'') +'</font></td>
<td width="80%" height="27"><font face="Verdana" size="1">'+ISNULL(CONVERT(VARCHAR(2000),LogInfo ),'')+'</font></td>
</tr>'
FROM #ErrorLogInfo ORDER BY ID;
/****** End to HTML Formatting ***/
SELECT
@TableHTML = @TableHTML + '</table>' +
'<p style="margin-top: 0; margin-bottom: 0"> </p>
<p> </p>'
IF (@MailProfile IS NOT NULL AND @MailID IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients = @MailID,
@subject = @strSubject,
@body = @TableHTML,
@body_format = 'HTML';
END
SELECT @TableHTML "HC_Report";
DROP TABLE #RebootDetails
DROP TABLE #FreeSpace;
DROP TABLE #BlkProcesses
DROP TABLE #ErrorLogInfo
DROP TABLE #CPU
DROP TABLE #Memory_BPool;
DROP TABLE #Memory_sys;
DROP TABLE #Memory_process;
DROP TABLE #Memory;
DROP TABLE #perfmon_counters;
DROP TABLE #PerfCntr_Data;
DROP TABLE #Backup_Report;
DROP TABLE #ConnInfo;
DROP TABLE #JobInfo;
DROP TABLE #tempdbfileusage;
DROP TABLE #LogSpace;
DROP TABLE #OpenTran_Detail;
SET NOCOUNT OFF;
SET ARITHABORT OFF;
END
