Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday 29 November 2017

To generate SQL Server Health Check Report via Database Mail in SQL

To generate SQL Server Health Check Report via Database Mail in SQL


Step 1:

we need to create following tables on SQL Server db

CREATE TABLE [dbo].[backup_healthcheck](
[server_name] [varchar](100) NOT NULL,
[database_name] [varchar](100) NOT NULL,
[LastFullBackup] [datetime] NULL,
[LastDifferential] [datetime] NULL,
[LastLog] [datetime] NULL
)



CREATE TABLE [dbo].[DB_status](
[server_name] [varchar](100) NOT NULL,
[name] [varchar](100) NULL,
[state_desc] [varchar](50) NULL



CREATE TABLE [dbo].[disk_details](
[server_name] [varchar](100) NOT NULL,
[Disk] [varchar](100) NOT NULL,
[FreeSpaceInGB] [varchar](100) NULL,
[TotalSpaceInGB] [varchar](100) NULL,
[FreeSpaceInPct] [varchar](100) NULL
)



CREATE TABLE [dbo].[Job_Details](
[Server_Name] [varchar](100) NOT NULL,
[Job_Name] [varchar](300) NOT NULL,
[Category] [varchar](400) NULL,
[Job_enabled] [varchar](10) NOT NULL,
[last_run_date] [varchar](100) NULL,
[last_run_status] [varchar](200) NULL
)





Step2:

Need to create Database  DB_Admin on SQL Server

Step 3:

Need to create SP from following script on DB_Admin db

CREATE  procedure [dbo].[usp_mon_Health_status_of_all_servers]      
      
       
      
as      
      
       
      
begin      
      
       
      
declare @sql nvarchar(4000)      
      
       
      
declare @dbs nvarchar(4000)      
      
       
      
declare @jobs nvarchar(4000)      
      
       
      
declare @disk nvarchar(4000)      
      
       
      
declare @return_code int      
      
       
      
declare @last_backup_date datetime      
      
       
      
       
      
declare @server_name sysname      
      
       
      
       
      
       
      
declare servers_cursor cursor for      
      
       
      
       
      
select srvname from master.dbo.sysservers where srvname not in ('repl_distributor','LOGSHIPLINK_USER-PC\MSSQL2012DESTSQL_-264832719')--('USER-PC\MSSQL2012SOURCE')      
      
      
order by srvname      
      
       
      
       
      
delete from backup_healthcheck      
      
       
      
       
      
delete from db_status      
      
       
      
       
      
delete from Job_Details      
      
       
      
delete from disk_details      
      
       
      
       
      
open servers_cursor      
      
       
      
fetch servers_cursor into @server_name      
      
       
      
while @@fetch_status = 0      
      
       
      
begin      
      
       
      
        set @sql = ''      
      
       
      
                   set @sql = 'insert into backup_healthcheck      
      
       
      
                   SELECT server_name = '''  + @server_name + ''',sdb.name,      
      
       
      
       
      
    MAX(CASE WHEN backupset.type = ''D'' THEN backupset.backup_finish_date ELSE NULL END) AS LastFullBackup,      
      
       
      
  MAX(CASE WHEN backupset.type = ''I'' THEN backupset.backup_finish_date ELSE NULL END) AS LastDifferential,      
      
    MAX(CASE WHEN backupset.type = ''L'' THEN backupset.backup_finish_date ELSE NULL END) AS LastLog      
      
                FROM [' + @server_name + '].master.sys.sysdatabases sdb      
      
       
      
LEFT OUTER JOIN [' + @server_name + '].msdb.dbo.backupset ON backupset.database_name = sdb.Name      
      
       
      
       
      
where sdb.name not in (''tempdb'',''ReportServer'',''ReportServerTempDB'')      
      
       
      
GROUP BY sdb.name      
      
       
      
ORDER BY sdb.name DESC'      
      
       
      
--print @sql      
      
        exec sp_executesql @sql      
      
       
      
       
      
--set @disk = ''      
      
--set @disk = 'insert into disk_details select distinct server_name = '''  + @server_name + ''',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      
      
--FROM [' + @server_name + '].master.sys.master_files mf      
      
--CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs'      
      
       
      
--        exec sp_executesql @disk      
      
       
      
       
      
set @dbs = ''      
      
       
      
set @dbs = 'insert into db_status select server_name = '''  + @server_name + ''',name,state_desc,getdate()       
      
       
      
       
      
       
      
FROM [' + @server_name + '].master.sys.databases order by name'      
      
       
      
--print @dbs      
      
       
      
       
      
       
      
exec sp_executesql @dbs      
      
       
      
       
      
       
      
set @jobs = ''      
      
       
      
set @jobs = 'insert into job_Details select server_name = '''  + @server_name +''',sj.name as job_name, sc.name as Category, sj.Enabled,      
      
       
      
        (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 [' + @server_name + '].msdb.dbo.sysjobhistory sjh where sjh.job_id = sj.job_id order by run_date desc, run_time desc) as last_run_date,      
      
       
      
CASE sjs.last_run_outcome      
      
       
      
WHEN 0 THEN ''Failed''      
      
       
      
WHEN 1 THEN ''Successful''      
      
       
      
WHEN 3 THEN ''Cancelled''      
      
       
      
WHEN 4 THEN ''In Progress''      
      
       
      
END AS LastRunStatus      
      
       
      
from [' + @server_name + '].msdb.dbo.sysjobs sj      
      
       
      
join [' + @server_name + '].msdb.dbo.syscategories sc      
      
       
      
        on sj.category_id = sc.category_id      
      
       
      
join [' + @server_name + '].msdb.dbo.sysjobservers sjs      
      
       
      
        on sjs.job_id = sj.job_id'      
      
       
exec sp_executesql @jobs      
      
  fetch servers_cursor into @server_name      
       
      
--print @jobs      
      
       
      
            
      
      
      
   Declare @emailSubject VARCHAR(100) ,@Body varchar(100),@sub varchar(1000)      
      
       
      
         set @sub='SQL Server Health Check Report for SQL Server'       
      
      
DECLARE @tableA_HTML  NVARCHAR(MAX) ;      
DECLARE @tableB_HTML  NVARCHAR(MAX) ;      
DECLARE @tableC_HTML  NVARCHAR(MAX) ;      
      
DECLARE @HTML  NVARCHAR(MAX) ;      
      
SET @tableA_HTML =      
    N'<H1>DB_status:</H1>' +      
    N'<table border="1">' +      
    N'<tr><th>server_name</th><th>name</th><th>state_desc</th><th>Status_date</th>     </tr>' +      
    CAST ( ( SELECT td =  server_name , '',      
                    td =  name , '',      
                    td =  state_desc, '',      
     td =   Status_date , ''       
              FROM DB_Admin.dbo.DB_status           
              FOR XML PATH('tr'), TYPE       
    ) AS NVARCHAR(MAX) ) +      
    N'</table>' ;      
      
       
SET @tableB_HTML =      
    N'<H1>Job_Details:</H1>' +      
    N'<table border="1">' +      
    N'<tr><th>Server_Name</th><th>Job_Name</th><th>Category</th><th>Job_enabled</th>  <th>last_run_date</th>       
 <th>last_run_status</th>  </tr>' +      
    CAST ( ( SELECT td =  Server_Name , '',      
                    td =  Job_Name , '',      
                    td =  Category, '',      
     td =  Job_enabled , '' ,      
     td =  last_run_date , '' ,      
     td =  last_run_status , ''       
              FROM DB_Admin.dbo.Job_Details           
              FOR XML PATH('tr'), TYPE       
    ) AS NVARCHAR(MAX) ) +      
    N'</table>' ;      
      
       
SET @tableC_HTML =      
    N'<H1>backup_healthcheck:</H1>' +      
    N'<table border="1">' +      
    N'<tr><th>server_name</th><th>database_name</th><th>LastFullBackup</th><th>LastDifferential</th>        
    <th>LastLog</th>       
       </tr>' +      
    CAST ( ( SELECT td =  server_name , '',      
                    td =  database_name , '',      
                    td =  LastFullBackup, '',      
     td =   LastDifferential , '' ,      
     td =   LastLog , ''       
              FROM DB_Admin.dbo.backup_healthcheck           
              FOR XML PATH('tr'), TYPE       
    ) AS NVARCHAR(MAX) ) +      
    N'</table>' ;      
      
      
      
      
SET @HTML = @tableA_HTML + ' <br/> ' +  @tableB_HTML + ' <br/> ' +  @tableC_HTML + '' ; -- Line Break      
SET @HTML = REPLACE(@HTML,'<th>','<th  bgcolor="Orange"/>' ); -- For BackGround Color       
      
      
      
   
      
      
      
end      
      
       
      
close servers_cursor      
      
       
      
       
      
deallocate servers_cursor      
      
       
 EXEC msdb.dbo.sp_send_dbmail      
      
    @recipients='servername@servermail.com;',      
      
    @profile_name = 'dbmailProfile',      
      
    @subject = @sub,      
      
    @body = @HTML,      
      
       @body_format = 'HTML'      
       
      
end      





Step 4:

Need to create SQL  Jobs

USE [msdb]
GO

/****** Object:  Job [DB_server_health_job]   
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 29-11-2017 18:01:30 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DB_server_health_job',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'USER-PC\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step 1]    Script Date: 29-11-2017 18:01:30 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec db_admin.dbo.usp_mon_Health_status_of_all_servers',
@database_name=N'DB_Admin',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Once a day',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20170911,
@active_end_date=99991231,
@active_start_time=115900,
@active_end_time=235959,
@schedule_uid=N'351f1f71-4248-400e-8a7f-60e5d2ea98b1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO



Step 5:

Need to Create linked server and configure Database Mail



Step 6:

Run the job to get the report

















No comments:

Post a Comment