Создание и автоматизация Отчета о статусе SQL Server при перезапуске сервера, либо по расписанию

04.06.2018 Автор:Пётр Окунев
1 038 Просмотров Комментариев нет

Build and Automate a SQL Server Status Report on Server Restart or ScheduledДанный материал является переводом оригинальной статьи "MSSQLTips : Build and Automate a SQL Server Status Report on Server Restart or Scheduled".

Знаете ли вы, когда перезапускался экземпляр SQL Server? Когда вы управляете множеством экземпляров SQL Server, вы можете не знать, когда перезапускается один из них. Поэтому неплохо было бы настроить отчёт, автоматически отправляемый по почте, чтобы узнавать о ситуации с тем или иным экземпляром SQL Server.

Эта статья показывает, как создать отчет о статусе SQL Server и отправить его вам на почту в случае перезапуска сервера или по заданному расписанию.

Предварительные требования

  • Включить email на вашем экземпляре. Смотрите Совет 1100 от Greg Robidoux по данной теме;
  • Далее, используем Совет 2347 от Bru Medishetty по HTML-форматированию электронных сообщений;
  • Время последнего перезапуска поможет выявить Совет 2501 от Greg Robidoux;
  • Создайте задание SQL Server и, если нужна помощь по системе Заданий SQL Server, смотрите Совет 4848 от Rick Dobson.

Шаг 1 – Создание хранимой процедуры для сбора важной информации

Совет: Используйте тестовый режим для отправки писем себе, потом выключите тестовый режим, для отправки в обычную группу мониторинга.

Вызов для запуска хранимой процедуры в тестовом режиме:

Exec [DBA].[dbo].[usp_sql_server_status_check_HTML] @Test='Yes'

Вызов для запуска хранимой процедуры при выключенном тестовом режиме:

Exec [DBA].[dbo].[usp_sql_server_status_check_HTML] @Test='No'

Хранимая процедура имеет много компонентов для проверки разных условий SQL Server.

Это первая часть хранимой процедуры.

-- File name : usp_sql_server_status_check_HTML.sql
Use [DBA] -- Name your administrative database here
GO   SET ANSI_NULLS ON
GO   SET QUOTED_IDENTIFIER ON
GO   begin try
   drop procedure [dbo].[usp_sql_server_status_check_HTML]
end try
begin catch
end catch
go   Create procedure [dbo].[usp_sql_server_status_check_HTML] @Test nvarchar(3)= null
as
BEGIN
set nocount on   -- File name : usp_sql_server_status_check_HTML.sql
-- Author    : Graham Okely B App Sc (IT)
-- Reference : https://www.mssqltips.com/sqlserverauthor/106/graham-okely/
Declare @Reference nvarchar(128) = 'https://www.mssqltips.com/sqlserverauthor/106/graham-okely/'
Declare @Report_Name nvarchar(128) = 'SQL Server Status Report'   -- Just in case @@Servername is null
Declare @Instance nvarchar(128) = ( Select isnull(@@Servername,cast(SERVERPROPERTY('MachineName') as nvarchar(128))+'\'+@@servicename) )   -- Get this many days from the SQL Agent log
Declare @Log_Days_Agent int = 4   -- Build a table for the report
Declare @SQL_Status_Report table ( Line_Number int NOT NULL identity(1,1),  Information nvarchar(max) )

Шаг 2 – Какие детали мы хотим видеть

Основные вещи, такие как имя экземпляра. Затем активный узел, где работает экземпляр. Это помогает при управлении кластерами, но не повредит и при одиночном экземпляре.

Вот несколько фрагментов кода, которые можно добавить к хранимой процедуре.

Добавляем информацию по версии SQL Server:

 -- Main title of the report
Insert into @SQL_Status_Report (Information)
Select 'SQL Server Status Check Report on '+  @Instance +' at '+cast(getdate() as nvarchar(28))  
Insert into @SQL_Status_Report (Information)
Select 'On node : '+ cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar(1024))  
Insert into @SQL_Status_Report (Information)
Select @@version
-- This line makes a blank row in the report
Insert into @SQL_Status_Report (Information) select ''

 

Добавляем время перезапуска SQL Server:

 -- Get the last restart date and time from sqlserver_start_time
Insert into @SQL_Status_report
SELECT 'Start time from DMV sqlserver_start_time ' + cast(sqlserver_start_time as nvarchar (28)) 
FROM sys.dm_os_sys_info

 

Добавляем свободное место на каждом диске, привязанном к экземпляру:

 -- Disk Drive Space
Insert into @SQL_Status_Report  (Information)
Select 'Drive space on '+@Instance +' (Lowest space free first)'
declare @drives  table( drive nvarchar(1), MbFree int)
insert into @drives
   exec xp_fixeddrives
Insert into @SQL_Status_Report  (Information)
select drive+ ' has  ' + cast (MbFree/1000 as nvarchar(20))+' GB Free' 
from @drives
Order by MbFree asc -- Show least amount of space first

 

Добавляем пользователей, созданных за последние X дней:

 -- Users added in the last X days
Declare @DaysBack int=7
Insert into @SQL_Status_Report  (Information)
Select 'Users added in last '+ cast(@DaysBack as nvarchar(12))+' days'
Insert into @SQL_Status_Report  (Information)
Select name+' '+type_desc+ ' '+ cast(create_date as nvarchar(28))+' ' + cast(datediff(day,create_date,getdate()) as nvarchar(12)) + ' days ago'
From sys.server_principals
Where type_desc in ('WINDOWS_LOGIN','WINDOWS_GROUP','SQL_LOGIN')
and datediff(day,create_date,getdate())<@DaysBack

 

Добавляем общее количество БД и объём занимаемого места:

 -- Gather summary of databases using sp_helpdb
Declare @sp_helpdb_results table( 
[db_name] nvarchar(256), [db_size] nvarchar(25), [owner] nvarchar(128), [db_id] int, 
[created_data] datetime, [status] nvarchar(max), [compatability] int)
INSERT INTO @sp_helpdb_results
          EXEC sp_helpdb
-- Flag databases with an unknown status
insert into @sp_helpdb_results( [db_name],[owner],[db_size])
   SELECT name,'Database Status Unknown' COLLATE database_default,0
   FROM sys.sysdatabases 
   Where [name] COLLATE database_default not in (Select [db_name] COLLATE database_default from @sp_helpdb_results)
-- Remove " MB"
UPDATE @sp_helpdb_results SET [db_size] = replace([db_size],' MB','')
Delete from @sp_helpdb_results Where [db_size]='0'
-- Report summary of databases using sp_helpdb
Insert into @SQL_Status_Report  (Information)
Select @Instance+' has ' +  cast(count(*) as nvarchar(8)) + ' databases with ' +
cast(cast(sum(cast(replace([db_size],' MB','')  as float)) as int)/1000 as nvarchar(20))  +  ' GB of data' 
FROM @sp_helpdb_results

 

Добавляем информацию о самой большой БД:

-- Database sizes
Insert into @SQL_Status_Report (Information)
Select 'Largest database on '+@Instance+' in MB' 
Insert into @SQL_Status_Report (Information)
Select top 1 [db_name] + ' ' + convert(nvarchar(10),round(convert(numeric,ltrim(replace([db_size],' Mb',''))),0)) 
From @sp_helpdb_results
Order by [db_size] desc

 

Добавляем дату старейшего бэкапа (чтобы понять, в порядке ли система резервного копирования):

 -- Oldest backup
Insert into @SQL_Status_Report (Information)
Select 'Oldest full database backup on '+@Instance 
Insert into @SQL_Status_Report (Information)
Select top 1 left(database_name,30)+' '+COALESCE(Convert(varchar(10), MAX(backup_finish_date), 121),'Not Yet Taken') 
From msdb..backupset 
Where database_name not in ( 'tempdb' )
and database_name in (select distinct name FROM master..sysdatabases)
and type = 'D' 
Group by database_name
Order by MAX(backup_finish_date) asc

 

Добавляем записи из журнала SQL Server Agent:

 -- Agent log information
Insert into @SQL_Status_Report  (Information)
Select 'Agent log check on '+@Instance+' Last ' +cast( @Log_Days_Agent as nvarchar(12))+ ' days' 
Declare @SqlAgenterrorLog table(logdate  datetime, [ProcessInfo]  varchar(29), errortext  varchar(max))
insert into @SqlAgenterrorLog
   exec sys.xp_readerrorlog 0,2
-- Report
Insert into @SQL_Status_Report  (Information)
Select DISTINCT cast(logdate as nvarchar(28))+' '+[ProcessInfo]+' '+left(errortext,300) 
from  @SqlAgenterrorLog
where logdate>dateadd(day,-@Log_Days_Agent,getdate()) 
order by 1 desc

 

Добавляем записи из журнала Ошибок SQL Server:

 -- Server log last 20 rows
Insert into @SQL_Status_Report (Information)
Select 'Sql Server log check on '+@Instance+' top 20 rows' 
Declare @SqlerrorLog table(logdate  datetime, [ProcessInfo]  varchar(29), errortext  varchar(max))
insert into @SqlerrorLog
   exec sys.xp_readerrorlog
Insert into @SQL_Status_Report (Information)
Select top 20 cast(logdate as nvarchar(28))+' '+[ProcessInfo]+' '+left(errortext,300) 
from  @SqlerrorLog 
order by 1 desc

 

Далее мы можем добавить любые собственные проверки. Следует лишь придерживаться шаблона из секций выше.

Ниже добавляем нижний колонтитул отчета (необходимо для закрытия отчета, а также для добавления END в нижнюю часть хранимой процедуры):

 -- Report Footer
Insert into @SQL_Status_Report (Information)
Select 'End of the ' + @Report_Name + ' on ' + @Instance + ' at ' + cast(getdate() as nvarchar(28))
Insert into @SQL_Status_Report (Information)
Select 'For more tips see : ' + @Reference   END

 

Шаг 3 – Форматирование и доставка Email

Теперь нам остаётся подготовить и отправить сообщение по электронной почте.

Вы можете добавить этот раздел, чтобы отправить отчет по электронной почте. Просто измените значения ниже, чтобы они соответствовали вашему профилю почты базы данных и адресам электронной почты, которые вы хотите получить.

 -- Prepare email
Declare @xml nvarchar(MAX)
Declare @body NVARCHAR(MAX)   SET @xml =  cast(( select ltrim(Information) as'td' 
FROM  @SQL_Status_Report ORDER BY Line_Number
FOR XML PATH('tr'), ELEMENTS ) AS nvarchar(MAX))   Declare @Subject_Line nvarchar(128) = 'SQL Server Status Report from ' + @Instance   SET @body ='<html><body><table border = 1 width="80%"><th><H3>' +@Subject_Line+'</H3></th>'   SET @body = @body + @xml +'</table></body></html>'
If ( @Test='Yes' )
BEGIN
   Set @Subject_Line = @Subject_Line + ' Test Mode' 
   EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'DBA', -- replace with your SQL Database Mail Profile 
   @body = @body,
   @body_format ='HTML',
   @recipients = 'Your.email@data.com', -- replace with your email address
   @subject = @Subject_Line;
   print @body
END
Else
BEGIN
   EXEC msdb.dbo.sp_send_dbmail 
   @profile_name = 'DBA', -- replace with your SQL Database Mail Profile 
   @body = @body, 
   @body_format ='HTML', 
   @recipients = 'monitoring_team@data.com', -- replace with the monitoring email address 
   @subject = @Subject_Line; 
END

 

Шаг 4 - Сборка

Скопируйте все части, которые вы хотите использовать сверху, в один файл и создайте хранимую процедуру. Кликните здесь для завершения создания хранимой процедуры.

 

Шаг 5 - Тестирование

Мы можем протестировать хранимую процедуру, используя следующие параметры:

 -- Send the email to the tester
Exec [DBA].[dbo].[usp_sql_server_status_check_HTML] @Test=’Yes’   -- Send the email to the monitoring group
Exec [DBA].[dbo].[usp_sql_server_status_check_HTML] @Test=’No’   -- Send the email to the monitoring group
Exec [DBA].[dbo].[usp_sql_server_status_check_HTML]

 

Шаг 6 - Расписание

Чтобы запланировать выполнение хранимой процедуры, мы можем создать Задание SQL Server Agent.

Развертывание путем создания Задания SQL Server Agent:

SQL Agent Job fo Server Status Report - General Tab

Один шаг работы - это все, что требуется.

SQL Agent Job fo Server Status Report - Job Step

Шаг задания Статус выхода

SQL Agent Job fo Server Status Report - Exit Code

При установке расписания задания будем ориентироваться, например, на выполнение Задания во время запуска и третий вторник каждого месяца.

SQL Server Agent Job fo Server Status Report - Schedule

 

Результирующий отчёт

Вот так выглядит пример письма, которое мы можем получить в конечном результате:

SQL Server Status Check Report via Email

Опубликовано в :  Microsoft SQL Server
Метки :  , , , , , , ,

Добавить комментарий