USE [DPAudit] GO /****** Object: StoredProcedure [dbo].[usp_SQLhealthcheck_report] Script Date: 29.07.2019 15:01:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /****************************************************************************/ /*************** SQL SERVER HEALTH CHECK REPORT - HTML **********************/ -- Tested: SQL Server 2008 R2, 2012, 2014 and 2016 -- Report Type: HTML Report Delivers to Mail Box -- Parameters: DBMail Profile Name *, Email ID *, Server Name (Optional); -- Reports: SQL Server Instance Details -- HA Status -- Last 4 days Critical Errors from ErrorLog -- Instance Last Recycle Information -- Tempdb File Usage -- Database File Info (20190711 - OK) -- Free Disk Space Report -- CPU Usage -- Memory Usage -- Performance Counters Data -- Missing Backup Report -- Connection Information -- Log Space Usage Report -- Job Status Report -- Blocking Report -- Long running Transactions /****************************************************************************/ /****************************************************************************/ --CREATE ALTER PROCEDURE [dbo].[usp_SQLhealthcheck_report] as declare @MailProfile NVARCHAR(200), @MailID NVARCHAR(2000), @Server VARCHAR(100) = NULL, @Header nvarchar(100) = NULL
--) --AS --select @@SERVERNAME IF @@SERVERNAME = 'PRIME' set @MailProfile = 'DBA' else set @MailProfile = 'DBA'
--set @MailID = '[email protected]' 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
/*************************************************************/ /****************** HA Status *****************/ /*************************************************************/
insert into #HaStatus select HARCS.replica_server_name, HARS.role, HARS.role_desc, HARS.operational_state_desc, HARS.connected_state_desc, HARS.recovery_health_desc, HARS.synchronization_health_desc, AGL.dns_name, AGL.ip_configuration_string_from_cluster, AGL.port from sys.dm_hadr_availability_replica_states HARS LEFT JOIN sys.dm_hadr_availability_replica_cluster_states HARCS ON HARS.replica_id=HARCS.replica_id LEFT JOIN sys.availability_group_listeners AGL ON HARS.group_id=AGL.group_id END
CREATE TABLE #SuspectPages( [Database_name] [sysname] NOT NULL, [page_id] [bigint] NOT NULL, [event_type] [int] NOT NULL, [error_count] [int] NOT NULL )
insert into #SuspectPages select name as Database_name, page_id, event_type, error_count from msdb..suspect_pages sp join master.sys.databases db on sp.database_id = db.database_id where sp.last_update_date > getdate()-7
/*************************************************************/ /****************** Errors audit for last 4 Days *************/ /*************************************************************/
/***********************************************************/ /************* 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));
CREATE TABLE #FreeSpace ( DiskDrive CHAR(3), DiskLabel nvarchar(50), DiskSizeMB BIGINT, DiskSizeGB DECIMAL(16,2), DiskFreeSpaceMB BIGINT, DiskFreeSpaceGB DECIMAL(16,2), DiskFreePercent DECIMAL(16,2) ) INSERT INTO #FreeSpace SELECT DiskDrive, DiskLabel, DiskSizeMB, CAST(DiskSizeMB / 1024.00 AS DECIMAL(16,2)) as DiskSizeGB, DiskFreeSpaceMB, CAST(DiskFreeSpaceMB / 1024.00 AS DECIMAL(16,2)) as DiskFreeSpaceGB, CONVERT(numeric(18,2), NULLIF(DiskFreeSpaceMB/DiskSizeMB,0.00)*100) AS DiskFreePercent FROM( SELECT DISTINCT dovs.logical_volume_name AS DiskLabel, dovs.volume_mount_point AS DiskDrive, CONVERT(numeric(18,2),dovs.total_bytes/1048576.0) AS DiskSizeMB, CONVERT(numeric(18,2),dovs.available_bytes/1048576.0) AS DiskFreeSpaceMB FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) dovs ) AS Tablo
--drop table #FreeSpace
/*************************************************************/ /************* 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 ***************/ /*************************************************************/
---- 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;
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;
INSERT INTO #Memory_process select -- oguz ROUND( physical_memory_in_use_kb/1048576.0, 2) AS 'physical_memory_in_use(GB)', ROUND( locked_page_allocations_kb/1048576.0, 2) AS 'locked_page_allocations(GB)', ROUND( virtual_address_space_committed_kb/1048576.0,2) AS 'virtual_address_space_committed(GB)', ROUND( available_commit_limit_kb/1048576.0,2) AS 'available_commit_limit(GB)', ROUND( page_fault_count,2) as 'page_fault_count' from sys.dm_os_process_memory;
CREATE TABLE #Memory( 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 '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 'Percentage_Used',Percentage_Used FROM #Memory_sys UNION SELECT 'system_memory_state_desc',system_memory_state_desc 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;
CREATE TABLE #PerfCntr_Data( 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 '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 'Page Life Expectency in seconds', cntr_value FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Page life expectancy' 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)' UNION ALL SELECT 'BufferCacheHitRatio', (a.cntr_value * 1.0 / b.cntr_value) * 100.0 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') AS P;
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;
-- 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
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 File Info *************************/ /*************************************************************/
exec sp_MSforeachdb ' Use [?]; INSERT INTO #FileUsage SELECT DISTINCT CONVERT(VARCHAR(100), @@SERVERNAME) AS [server_name], db_NAME() as DatabaseName, GETDATE() AS [Timestamp], [TYPE] = A.TYPE_DESC, [FILE_Name] = A.name, [FILEGROUP_NAME] = fg.name, [FILESIZE_MB] = CONVERT(DECIMAL(10, 2), A.size / 128.0), CASE WHEN mf.[max_size] = -1 THEN -1 -- Unlimited ELSE mf.[max_size] * CONVERT(FLOAT, 8) / 1024.0 END [MaxSize], [USEDSPACE_MB] = CONVERT(DECIMAL(10, 2), A.size / 128.0 - ((A.size / 128.0) - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT) / 128.0)), [FREESPACE_MB] = CONVERT(DECIMAL(10, 2), A.size / 128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT) / 128.0), [FREESPACE_%] = CONVERT(DECIMAL(10, 2), ((A.size / 128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT) / 128.0) / (A.size / 128.0)) * 100), LTRIM(CASE mf.[is_percent_growth] WHEN 1 THEN STR(a.[growth]) +'' %'' ELSE STR(a.[growth]*CONVERT(FLOAT,8)/1024)+'' MiB'' END) [AutoGrowth], [File_Location] = A.physical_name
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id INNER JOIN sys.master_files mf ON A.file_guid = mf.file_guid WHERE A.TYPE_DESC NOT LIKE ''LOG'' and db_NAME() != ''tempdb'' --ORDER BY A.TYPE desc, A.NAME;'
;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;
/*************************************************************/ /****************** HTML Preparation *************************/ /*************************************************************/
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; DROP TABLE #FileUsage; DROP TABLE #SuspectPages;
