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
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