MANUAL PRACTICO JOB OVERVIEW El job Overview est habilitado y programado dentro del servidor Dresden,
el cual se ejecuta cada dos horas durante las horas hbiles (en horas pares) y cada 4 horas para los horas no hbiles (en horas impares).
Este job est conformado por 16 pasos: 1. Paso: Se obtiene el tiempo de la ltima ejecucin del Job y se guarda en la tabla [rep].[SQL_Overview_Last_Run_Date]
-- Get Job Name set nocount on DECLARE @JobName sysname SELECT @JobName = [name] from [Link] WHERE job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) PRINT '>'+@JobName+'<' -- Get the date of the last successfull execution of this job DECLARE @dtLastRun datetime DECLARE @chLastRun char(26) SELECT top 1 @dtLastRun = CAST(CAST(run_date AS char(8)) + ' ' + -- Convert run_date to DateTime data type STUFF(STUFF( -- Insert : into Time RIGHT('000000' + -- Add leading Zeros CAST(run_time AS varchar(6)) ,6) , 3, 0, ':'), 6, 0, ':') AS datetime) FROM [Link] A, [Link] B WHERE A.job_id = B.job_id AND B.run_status = 1 AND [Link] = @JobName ORDER BY 1 DESC
SELECT 'Job Last Run Date', @dtLastRun IF @dtLastRun is NULL SET @dtLastRun = '1900-01-01' SET @chLastRun = Convert(char(26),@dtLastRun) PRINT 'Job Last Run Date = ' + @chLastRun DELETE FROM [rep].[SQL_Overview_Last_Run_Date] INSERT INTO [rep].[SQL_Overview_Last_Run_Date ([LastJobRun_date]) VALUES (@chLastRun)
2. Paso: Crea las tablas temporales en la BD tempdb y las cuales van a ser utilizadas por el paquete overview.
IF EXISTS (SELECT * FROM [Link] WHERE id = OBJECT_ID(N'[tempdb].[dbo].[HoldforEachDB]') ) DROP TABLE [tempdb].[dbo].[HoldforEachDB] GO CREATE TABLE [tempdb].[dbo].[HoldforEachDB] ( [Server] nvarchar(128) NULL, [InstanceName] nvarchar(128) NULL, [DatabaseName] nvarchar(128) NOT NULL, [FileId] smallint NOT NULL, [GroupId] smallint NOT NULL, [Size] real NOT NULL, [MaxSize] real not null, [Growth] real NOT NULL, [FileStatus] [int] NOT NULL, [LogicalName] nvarchar(128) NOT NULL, [Filename] nvarchar(260) NOT NULL, [DatabaseStatus] nvarchar(128) NULL, [Updateability] nvarchar(128) NULL, [User_Access] nvarchar(128) NULL, [Recovery] nvarchar(128) NULL, [SpaceUsed(MB)] real , [UsageType] varchar (6), [Owner] nvarchar(128) NULL, [CreateDate] datetime NULL, [CmptLevel] tinyint NULL, [DateChecked] datetime ) ON [PRIMARY] GO If Object_Id('[Link]') is Not Null Drop table [Link] GO CREATE TABLE [Link]( [Server] [nvarchar](128) NOT NULL, [dtMessage] [datetime] NULL, [SPID] [varchar](50) NULL, [vchMessage] [nvarchar](1024) NULL,
[ID] [int] NULL ) ON [PRIMARY] GO IF EXISTS (SELECT * FROM [Link] WHERE id = OBJECT_ID(N'[tempdb].[dbo].[HoldDiskSpace]') ) DROP TABLE [tempdb].[dbo].[HoldDiskSpace] GO CREATE TABLE [tempdb].[dbo].[HoldDiskSpace] ( [Server] nvarchar(128), Drive char(1) PRIMARY KEY, [Free(MB)] int NULL, [Total(MB)] int NULL, [Free(%)] tinyint NULL, [DateChecked] [datetime] NOT NULL CONSTRAINT [DF_HoldDiskSpace_Date Checked] DEFAULT (getdate()), ) GO IF EXISTS (SELECT * FROM [Link] WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Hold_XP_MSVER]') ) DROP TABLE [tempdb].[dbo].[Hold_XP_MSVER] GO CREATE TABLE [tempdb].[dbo].[Hold_XP_MSVER] ( IDX INT NULL, C_NAME VARCHAR(100) NULL, INT_VALUE FLOAT NULL, C_VALUE VARCHAR(128) NULL) GO IF EXISTS (SELECT * FROM [Link] WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Job_Info]') ) DROP TABLE [tempdb].[dbo].[Job_Info] GO CREATE TABLE [tempdb].[dbo].[Job_Info] ( [job_id] [uniqueidentifier] NULL , [originating_server] [nvarchar] (30) NULL , [name] [nvarchar] (128) NULL , [enabled] [tinyint] NULL , [description] [nvarchar] (512) NULL , [start_step_id] [int] NULL , [category] [sysname] NULL , [owner] [nvarchar] (128) NULL , [notify_level_eventlog] [int] NULL , [notify_level_email] [int] NULL , [notify_level_netsend] [int] NULL , [notify_level_page] [int] NULL , [notify_email_operator] [sysname] NULL , [notify_netsend_operator] [sysname] NULL ,
[notify_page_operator] [sysname] NULL , [delete_level] [int] NULL , [date_created] [datetime] NULL , [date_modified] [datetime] NULL , [version_number] [int] NULL , [last_run_date] [int] NULL , [last_run_time] [int] NULL , [last_run_outcome] [int] NULL , [next_run_date] [int] NULL , [next_run_time] [int] NULL , [next_run_schedule_id] [int] NULL , [current_execution_status] [int] NULL , [current_execution_step] [sysname] NULL , [current_retry_attempt] [int] NULL , [has_step] [int] NULL , [has_schedule] [int] NULL , [has_target] [int] NULL , [type] [int] NULL , [last_run_duration] [int] NULL , [last_outcome_message] [nvarchar] (1024) NULL , [Package_run_date] [datetime] NOT NULL CONSTRAINT [DF_Package_run_date] (getdate()), ) ON [PRIMARY] GO IF EXISTS (SELECT * FROM [Link] WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Job_Activity]') ) DROP TABLE [tempdb].[dbo].[Job_Activity] GO CREATE TABLE [tempdb].[dbo].[Job_Activity]( [Server] [nvarchar](128) NOT NULL CONSTRAINT [DF_Job_Activity_Server] (@@servername), [session_id] [int] NOT NULL, [job_id] [uniqueidentifier] NOT NULL, [job_name] [sysname] NOT NULL, [run_requested_date] [datetime] NULL, [run_requested_source] [sysname] NULL, [queued_date] [datetime] NULL, [start_execution_date] [datetime] NULL, [last_executed_step_id] [int] NULL, [last_executed_step_date] [datetime] NULL, [stop_execution_date] [datetime] NULL, [next_scheduled_run_date] [datetime] NULL, [job_history_id] [int] NULL, [message] [nvarchar](1024) NULL, [run_status] [int] NULL, [operator_id_emailed] [int] NULL, [operator_id_netsent] [int] NULL, [operator_id_paged] [int] NULL, [Row_inserted_date] [datetime] NOT NULL CONSTRAINT [DF_Row_inserted_date] (getdate()) ) ON [PRIMARY] GO
DEFAULT
DEFAULT
DEFAULT
3. Paso: Recolecta la informacin a travs de la ejecucin del paquete Overview e inserta la informacin recolectada en las tablas de la BD Overview del servidor Dresden. Antes de actualizar la informacin contenida en dichas tablas, se hace una actualizacin de dicha informacin a otras tablas con el sufijo _hist con el fin de conservar los histricos de la informacin obtenida a travs del paquete Overview.
Esta es la estructura del paquete (ver Informacin en Paquete Overview)
4. Paso: Ejecuta el job Report Job failures a travs de la sentencia:
EXEC dbo.sp_start_job N'DBA-SQL Overview - Report Job Failures'
En dicho job se revisa la tabla sql_overview.dbo.Job_History_report y enva un correo a los usuarios que estn incluidos en la tabla sql_overview.dbo.SQL_PersonasNotificacion con el asunto 'SQL Server - Job Failures Detected' si encuentra registros en dicha tabla despus de hacer una consulta desde la ltima fecha que corri el job contra la informacin de la tabla sql_overview.dbo.Job_History (alimentada con el paquete Overview) con los job en estado 0, a continuacin se transcribe la estructura:
set nocount on DECLARE @dtLastRun datetime DECLARE @chLastRun char(26) SELECT @dtLastRun = LastJobRun_date from rep.SQL_Overview_Last_Run_Date SELECT 'Last Run Date', @dtLastRun SELECT @dtLastRun = ISNULL (@dtLastRun ,DATEADD(dd,-1,current_timestamp))
SET @chLastRun = Convert(char(26),@dtLastRun) PRINT 'Job Last Run Date = ' + @chLastRun -- Using the Last Run Date, check if there are failed jobs since the last -- execution of this job. truncate table job_history_report insert into job_history_report(server,name,step_id,Run_start_datetime,Run_end_datetime,Message) SELECT [Server],[name],[step_id],convert(varchar(16),Run_start_datetime,112),convert(varchar(16),Run_end_dat etime,112),[Message] FROM sql_overview.dbo.Job_History where run_status = 0 and datediff(ms, @chLastRun ,Run_End_datetime ) > 0 order by Run_start_datetime,step_id update jr set id_cliente=sl.id_cliente from job_history_report jr inner join SSIS_ServerList sl on [Link]=[Link] -- Jobs failures found send e-mail IF (select COUNT(*) from job_history_report)> 0 BEGIN -- Send E-Mail if new failed Jobs are Detected DECLARE @From varchar (100) DECLARE @Subject varchar (100) DECLARE @OutputFile varchar (100) DECLARE @Statement varchar (500) DECLARE @TableHTML NVARCHAR(MAX) declare @min int declare @max int declare @client int declare @clientes table (id int not null identity(1,1),id_cliente int) declare @MailsEnviar as varchar(max) declare @i as int create table #tabla (i int identity(1,1),mail varchar(150)) insert into @clientes(id_cliente) select id_cliente from job_history_report where id_cliente<>-1 and id_cliente in (select id_cliente from sql_overview.dbo.SQL_PersonasNotificacion where id_cliente is not null) group by id_cliente set @min=0 set @max=isnull((select MAX(id) from @clientes),0) while @min<@max begin set @min=@min+1 select @client=id_cliente from @clientes where id=@min truncate table #tabla insert into #tabla select mail from sql_overview.dbo.SQL_PersonasNotificacion where id_cliente=@client
set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/ WHILE @i <=(SELECT MAX(i) from #tabla) BEGIN if @i = 1 set @MailsEnviar = (Select mail from #tabla where i = @i) ELSE set @MailsEnviar = @MailsEnviar + ';' + (Select mail from #tabla where i = @i) set @i = @i+1 END SELECT @From=@@SERVERNAME SELECT @Subject='SQL Server - Job Failures Detected' SET @TableHTML = N'<table border="1">' + N'<tr>' + N'<th>Server</th>' + N'<th>name</th>' + N'<th>step_id</th>' + N'<th>Run_start_datetime</th>' + N'<th>Run_end_datetime</th>' + N'<th>message</th>' + N'</tr>' + CAST ( ( SELECT td=[Server],'' ,td=[name],'' ,td=[step_id],'' ,td=Run_start_datetime,112,'' ,td=Run_end_datetime,112,'' ,td=[Message] FROM sql_overview.dbo.Job_History_report where id_cliente=@client FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar , @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; end declare @tablad as table(i int identity(1,1),mail varchar(150)) insert into @tablad select mail from sql_overview.dbo.SQL_PersonasNotificacion where id_cliente=-1 set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/ WHILE @i <=(SELECT MAX(i) from @tablad) BEGIN if @i = 1 set @MailsEnviar = (Select mail from @tablad where i = @i)
ELSE set @MailsEnviar = @MailsEnviar + ';' + (Select mail from @tablad where i = @i) set @i = @i+1 END SELECT @From=@@SERVERNAME SELECT @Subject='SQL Server - Job Failures Detected' SET @TableHTML = N'<table border="1">' + N'<tr>' + N'<th>Server</th>' + N'<th>name</th>' + N'<th>step_id</th>' + N'<th>Run_start_datetime</th>' + N'<th>Run_end_datetime</th>' + N'<th>message</th>' + N'</tr>' + CAST ( ( SELECT td=[Server],'' ,td=[name],'' ,td=[step_id],'' ,td=Run_start_datetime,112,'' ,td=Run_end_datetime,112,'' ,td=[Message] FROM sql_overview.dbo.Job_History_report FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar , @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; END
5. Paso: Ejecuta el Job Report Database Missing backups
EXEC dbo.sp_start_job N'DBA-SQL Overview - Report Databases Missing Backups'
Dicho job contiene 3 pasos:
En el primer paso actualiza la lista de base datos [rep].[Database_List] en la cual se lleva el control de las bases de datos a las cuales se les va a revisar el backup con mximo de 1 da de acuerdo con la consulta a la tabla [Database_Status] obtenida del paquete overview contra la tabla [SSIS_ServerList] la cual contiene las instancias de produccin que se controlan por los DBA.
SELECT [Database_Status].[Server] ,[Database_Status].[DatabaseName],NULL,NULL ,[SSIS_ServerList].[Usage] FROM [Database_Status] INNER JOIN [SSIS_ServerList] ON [SSIS_ServerList].[InstanceName] like [Database_Status].[Server] + '%' Where NOT EXISTS (select * from [rep].[Database_List] WHERE [Database_List].[Server] = [Database_Status].[Server] AND [Database_List].[DatabaseName] = [Database_Status].[DatabaseName] ) ORDER BY 1,2 INSERT INTO [rep].[Database_List] ([Server],DatabaseName,Usage) SELECT [Database_Status].[Server] ,[Database_Status].[DatabaseName] ,isNULL([SSIS_ServerList].[Usage],'NA') FROM [Database_Status] INNER JOIN [SSIS_ServerList] ON [SSIS_ServerList].[InstanceName] like [Database_Status].[Server] + '%' Where NOT EXISTS (select * from [rep].[Database_List] WHERE [Database_List].[Server] = [Database_Status].[Server] AND [Database_List].[DatabaseName] = [Database_Status].[DatabaseName] ) UPDATE [rep].[Database_List] SET [AllowableDaysBetweenBackups] = NULL WHERE [DatabaseName] in ('TempDB','Pubs','Northwind','model') AND [AllowableDaysBetweenBackups] IS NOT NULL DECLARE @dtLastRun datetime SELECT @dtLastRun = LastJobRun_date from rep.SQL_Overview_Last_Run_Date SELECT @dtLastRun = ISNULL (@dtLastRun ,DATEADD(dd,-1,current_timestamp)) PRINT 'Last Run Date = ' + Convert(char(26),@dtLastRun) UPDATE [rep].[Database_List] SET [AllowableDaysBetweenBackups] = 1 WHERE [AllowableDaysBetweenBackups] IS NULL and exists (SELECT 1 FROM dbo.Backup_History AS c WHERE [rep].[Database_List] .Server = Server AND [rep].[Database_List] .DatabaseName = c.Database_Name AND c.backup_finish_date > @dtLastRun )
En el Segundo paso enva el correo 'SQL Server - Databases Missing Database Backups' a la lista de correos anteriormente citada si existe informacin en la vista rep.vw_DatabasesMissingBackups
set nocount on
-DECLARE @Subject varchar (100) SET @Subject='SQL Server - Databases Missing Database Backups' declare @Count as int select @Count=count(*) from rep.vw_DatabasesMissingBackups print @Count IF @Count > 0 Begin DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<table border="1">' + N'<tr><th>Server</th>' + N'<th>Database</th>' + N'<th>Status</th>' + N'<th>Usage</th>' + N'<th>Project</th>' + N'<th>Days<br>Crit.</th>' + N'<th>DB Status</th>' + N'<th>Latest Backup</th></tr> ' + CAST ( ( SELECT td=[server],'' ,td=DatabaseName,'' ,td=[Status],'' ,td=Usage,'' ,td=[ProjectName],'' ,td=[AllowableDaysBetweenBackups],'' ,td=DatabaseStatus,'' ,td=LastBackup,'' from rep.vw_DatabasesMissingBackups order by 1,2,3,4,5 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; /*Declara variables*/ declare @MailsEnviar as varchar(max) declare @i as int declare @tabla as table(i int identity(1,1) ,mail varchar(150)) /*Define valores de variables*/ insert into @tabla select mail from sql_Overview.dbo.SQL_PersonasNotificacion where id_cliente=-1 set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/ WHILE @i <=(SELECT MAX(i) from @tabla) BEGIN if @i = 1 BEGIN set @MailsEnviar = (Select mail from @tabla where i = @i) END ELSE BEGIN set @MailsEnviar = @MailsEnviar + ';' + (Select mail from @tabla where i = @i) END
set @i = @i+1 END EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar, @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; END
La vista rep.vw_DatabasesMissingBackups revisa las bases de datos de la [rep].[Database_List] contra la tabla dbo.Backup_History para revisar para cuales no hay backup de las ltimas 24 horas.
USE [sql_overview] GO /****** Object: View [rep].[vw_DatabasesMissingBackups] Script Date: 01/25/2012 14:53:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [rep].[vw_DatabasesMissingBackups] AS SELECT [Link], [Link] AS DatabaseName, 'No Backups Found' AS Status, [Link], ISNULL([Link], 'NA') AS ProjectName, [Link],DatabaseStatus, NULL AS LastBackup FROM dbo.Database_Status AS a INNER JOIN rep.Database_List AS b ON [Link] = [Link] AND [Link] = [Link] AND [Link] IS NOT NULL WHERE (NOT EXISTS (SELECT 1 AS Expr1 FROM dbo.Backup_History AS c WHERE ([Link] = Server) AND ([Link] = c.Database_Name) AND (c.backup_type = 'database'))) UNION ALL SELECT [Link], [Link], 'Needs Newer Backup' AS status, [Link], ISNULL([Link], ' ') AS ProjectName, [Link] AS 'Days Allowed Since Backup',DatabaseStatus, MAX(c.backup_finish_date) AS 'Latest_Backup' FROM dbo.Database_Status AS a INNER JOIN rep.Database_List AS b ON [Link] = [Link] AND [Link] = [Link] AND [Link] IS NOT NULL INNER JOIN dbo.Backup_History AS c ON [Link] = [Link] AND c.Database_Name = [Link] AND c.backup_type = 'database' WHERE (NOT EXISTS (SELECT 1 AS Expr1 FROM dbo.Backup_History c WHERE ([Link] = Server) AND ([Link] = c.Database_Name) AND (c.backup_type IN ('Differential', 'database')) AND (c.backup_finish_date > DATEADD(dd, [Link] * - 1, (CURRENT_TIMESTAMP+0.006777778))))) GROUP BY [Link], [Link], [Link],[Link], [Link], [Link]
GO
En el tercer paso hace lo mismo que en el anterior paso para los log de las bases de datos enviando en correo 'SQL Server - Databases Missing Log Backups' , a continuacin se transcribe el script, utilizando la vista rep.vw_DatabasesMissingLogBackups.
set nocount on -- Send E-Mail if new failed Jobs are Detected DECLARE @Subject varchar (100) SET @Subject='SQL Server - Databases Missing Log Backups' declare @Count as int select @Count=count(*) from rep.vw_DatabasesMissingLogBackups print @Count IF @Count > 0 Begin DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<table border="1">' + N'<tr><th>Server</th><th>Database</th>' + N'<th>Recovery</th><th>Status</th><th>Usage</th><th>Project</th>' + N'<th>Latest Backup</th></tr>' + CAST ( ( SELECT td=[Server],'' ,td=DatabaseName,'' ,td=[Recovery],'' ,td=[Status],'' ,td=Usage,'' ,td=[ProjectName] ,'' ,td=LastBackup,'' from rep.vw_DatabasesMissingLogBackups order by 1,2,3,4,5 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; /*Declara variables*/ declare @MailsEnviar as varchar(max) declare @i as int declare @tabla as table(i int identity(1,1) ,mail varchar(150)) /*Define valores de variables*/ insert into @tabla select mail from sql_Overview.dbo.SQL_PersonasNotificacion where id_cliente=-1 set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/ WHILE @i <=(SELECT MAX(i) from @tabla) BEGIN if @i = 1 BEGIN set @MailsEnviar = (Select mail from @tabla where i = @i) END ELSE
BEGIN set @MailsEnviar = @MailsEnviar + ';' + (Select mail from @tabla where i = @i) END set @i = @i+1 END EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar, @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; END
La estructura de la vista rep.vw_DatabasesMissingLogBackups, es:
USE [sql_overview] GO /****** Object: View [rep].[vw_DatabasesMissingLogBackups] Script Date: 01/25/2012 15:05:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [rep].[vw_DatabasesMissingLogBackups] AS SELECT [Link], [Link], [Link], 'No Log Backups Found' AS Status, [Link], ISNULL([Link], '') AS ProjectName, NULL AS LastBackup FROM dbo.Database_Info AS a INNER JOIN rep.Database_List AS b ON [Link] = [Link] AND [Link] = [Link] AND [Link] IN ('FULL', 'BULK_LOGGED') AND [Link] <> 'Model' AND [Link] = 'log' WHERE (NOT EXISTS (SELECT 1 AS Expr1 FROM dbo.Backup_History AS c WHERE ([Link] = Server) AND ([Link] = c.database_name) AND (c.backup_type = 'log'))) UNION ALL SELECT [Link], [Link], [Link], 'Log backup required at least every 24 hours' AS status, [Link], ISNULL([Link], '') AS [project name], MAX(c.backup_finish_date) AS 'Latest Backup' FROM dbo.Database_Info AS a INNER JOIN rep.Database_List AS b ON [Link] = [Link] AND [Link] = [Link] AND [Link] IN ('FULL', 'BULK_LOGGED') AND [Link] <> 'Model' INNER JOIN dbo.Backup_History AS c ON [Link] = [Link] AND c.database_name = [Link] AND c.backup_type = 'log' WHERE (NOT EXISTS (SELECT 1 AS Expr1 FROM dbo.Backup_History c WHERE ([Link] = Server) AND ([Link] = c.database_name) AND (c.backup_type = 'Log') AND (c.backup_finish_date > DATEADD(dd, - 1, CURRENT_TIMESTAMP+0.006777778)))) GROUP BY [Link], [Link], [Link], [Link], [Link], [Link]
GO
6. Paso: Ejecuta el Job - Report Drives Very Low on Space
EXEC dbo.sp_start_job N'DBA-SQL Overview - Report Drives Very Low on Space'
Dicho Job enva el correo 'SQL Server - Servers Very Low on Disk Space' despus de verificar la informacin contenida en la tabla [rep].[Disk_Space_Alert] contra la tabla [dbo].[Disk_Space] (trada con el paquete overview) y teniendo en cuenta un margen del 10% de espacio disponible para todas las unidades a los correos de la lista ya mencionada. La estructura del Job mencionado es:
-- This SQL script is used to report when disk space on a server is below 10% -- When the free space drops more another e-mail is sent -- When free space gets to be more than 10% it is removed DECLARE @MinFreespace as TINYINT SET @MinFreespace = 10 DECLARE @Subject varchar (100) SET @Subject='SQL Server - Servers Very Low on Disk Space' -- Delete any alerts that were below 10% free and are now above 10% DELETE FROM [rep].[Disk_Space_Alert] WHERE EXISTS (SELECT 1 FROM [dbo].[Disk_Space] B WHERE [Disk_Space_Alert].[Server] = B.[Server] AND [Disk_Space_Alert].[Drive] = B.[Drive] AND B.[Free(%)] > @MinFreespace ) PRINT 'Old Alerts Deleted Count=' + CAST (@@ROWCOUNT AS VARCHAR(9)) DECLARE @Count as int SELECT @Count = COUNT(*) FROM [dbo].[Disk_Space] a WHERE A.[Free(%)] < @MinFreespace AND NOT EXISTS (Select 1 FROM rep.Disk_Space_Alert b WHERE A.[Server] = B.[Server] AND A.[Drive] = B.[Drive] AND A.[Free(%)] <= B.[Free(%)] ) PRINT 'New Alerts Detected Count=' + CAST (@Count AS VARCHAR(9)) IF @Count > 0 BEGIN INSERT INTO [rep].[Disk_Space_Alert] ([Server] ,[Drive] ,[Total(MB)] ,[Free(MB)] ,[Free(%)] ,[DateFirstReported]) SELECT [Server]
,[Drive] ,[Total(MB)] ,[Free(MB)] ,[Free(%)] ,Getdate() FROM [dbo].[Disk_Space] a WHERE A.[Free(%)] < @MinFreespace AND NOT EXISTS (Select 1 FROM rep.Disk_Space_Alert b WHERE A.[Server] = B.[Server] AND A.[Drive] = B.[Drive] ) PRINT 'New Alerts Inserted Count=' + CAST (@@ROWCOUNT AS VARCHAR(9)) UPDATE A SET [Free(%)] = B.[Free(%)] FROM [rep].[Disk_Space_Alert] A INNER JOIN [dbo].[Disk_Space] B ON A.[Server] = B.[Server] AND A.[Drive] = B.[Drive] WHERE B.[Free(%)] < @MinFreespace PRINT '* Alerts Updated Count=' + CAST (@@ROWCOUNT AS VARCHAR(9)) SELECT * FROM [rep].[Disk_Space_Alert] DECLARE @TableHTML NVARCHAR(MAX) ; SET @TableHTML = N'<table border="1">' + N'<tr>' + N'<th>Server</th>' + N'<th>Drive</th>' + N'<th>Total(MB)</th>' + N'<th>Free(MB)</th>' + N'<th>Free(%)</th>' + N'<th>Date First Reported</th>' + N'</tr>' + CAST ( ( SELECT td=[Server],'' ,td=[Drive],'' ,td=[Total(MB)],'' ,td=[Free(MB)],'' ,td=[Free(%)],'' ,td=[DateFirstReported] FROM [rep].[Disk_Space_Alert] ORDER BY [DateFirstReported] DESC,1,2 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; /*Declara variables*/ declare @MailsEnviar as varchar(max) declare @i as int declare @tabla as table(i int identity(1,1),mail varchar(150)) /*Define valores de variables*/ insert into @tabla select mail from sql_overview.dbo.SQL_PersonasNotificacion where tipoper in ('TELMEX1','DBA','Operador') set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/
WHILE @i <=(SELECT MAX(i) from @tabla) BEGIN if @i = 1 BEGIN set @MailsEnviar = (Select mail from @tabla where i = @i) END ELSE BEGIN set @MailsEnviar = @MailsEnviar + ';' + (Select mail from @tabla where i = @i) END set @i = @i+1 END EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar, @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; END
7. Paso: Se ejecuta el Job Report Large Log File
EXEC [Link].sp_start_job N'DBA-SQL Overview - Report Large Log File'
Este Job enva el correo 'SQL Server - Databases with Log File Larger than the Database File (Maybe Shrink or Backup Log)', si encuentra informacin en la vista rep.vw_DatabasesWithLogFileGreaterThanDatabaseFiles la cual realiza una consulta sobre la tabla dbo.database_info que es alimentada por el paquete overview con las condicin de que el tamao del archivo se > 50 M y que el tamao del archivo de la DATA sea menor que el del archivo del log de la BD. Esta es la estructura del Job:
set nocount on -- Send E-Mail if new failed Jobs are Detected DECLARE @Subject varchar (100) SET @Subject='SQL Server - Databases with Log File Larger than the Database File (Maybe Shrink or Backup Log)' declare @Count as int select @Count=count(*) from rep.vw_DatabasesWithLogFileGreaterThanDatabaseFiles print @Count IF @Count > 0 Begin DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<table border="1">' + N'<tr><th>Server</th><th>Database</th>' + N'<th>Recovery</th>' + N'<th>DataSpace(MB)</th>' + N'<th>DataUsed(MB)</th>' + N'<th>LogSpace(MB)</th>' + N'<th>LogUsed(MB)</th>' + N'</tr>' +
CAST ( ( SELECT td=[Server],'' ,td=DatabaseName,'' ,td=[Recovery],'' ,td=[Data Space Allocated (MB)],'' ,td=[Data Space Used (MB)],'' ,td=[Log Space Allocated (MB)],'' ,td=[Log Space Used (MB)],'' from rep.vw_DatabasesWithLogFileGreaterThanDatabaseFiles order by 1,2,3,4,5 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; /*Declara variables*/ declare @MailsEnviar as varchar(max) declare @i as int declare @tabla as table(i int identity(1,1),mail varchar(150)) /*Define valores de variables*/ insert into @tabla select mail from sql_overview.dbo.SQL_PersonasNotificacion where tipoper in ('DBA','Operador') set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/ WHILE @i <=(SELECT MAX(i) from @tabla) BEGIN if @i = 1 BEGIN set @MailsEnviar = (Select mail from @tabla where i = @i) END ELSE BEGIN set @MailsEnviar = @MailsEnviar + ';' + (Select mail from @tabla where i = @i) END set @i = @i+1 END EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar, @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; END
Esta es la estructura de rep.vw_DatabasesWithLogFileGreaterThanDatabaseFiles:
la
vista
USE [sql_overview] GO /****** Object: View [rep].[vw_DatabasesWithLogFileGreaterThanDatabaseFiles] 01/25/2012 15:31:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
Script Date:
ALTER VIEW [rep].[vw_DatabasesWithLogFileGreaterThanDatabaseFiles] AS select A.[Server] ,A.[InstanceName] ,[Link] ,[Link] ,CAST ( SUM( A.[Size(MB)]) AS BIGINT) As [Data Space Allocated (MB)] ,CAST ( SUM( A.[SpaceUsed(MB)] ) AS BIGINT) As [Data Space Used (MB)] ,CAST (B.[Size(MB)] AS BIGINT) As [Log Space Allocated (MB)] ,CAST (B.[SpaceUsed(MB)] AS BIGINT) As [Log Space Used (MB)] from dbo.database_info a inner join dbo.database_info b on A.[Server] = B.[Server] and A.[InstanceName] = B.[InstanceName] and [Link] = [Link] and [Link] = 'DATA' and [Link] = 'Log' WHERE B.[Size(MB)] > 50 GROUP BY A.[Server] ,A.[InstanceName] ,[Link] ,[Link] ,B.[Size(MB)] ,B.[SpaceUsed(MB)] HAVING SUM( A.[Size(MB)] ) < B.[Size(MB)] GO
8. Paso: Ejecuta el Job Report ErrorLog Messages
EXEC [Link].sp_start_job N'DBA-SQL Overview - Report ErrorLog Messages'
En este Job se enva el correo 'SQL Server - ErrorLog Messages Requiring Further Review', con la informacin de los errores contenidos en la tabla temporal ##ErrorLog que es creada con base en la informacin de la tabla [SQL_Overview].[dbo].[ErrorLog] filtrada segn el texto del mensaje de error obtenido a travs de la ejecucin del paquete Overview (si hay ms de 100 mensajes de error se crea la tabla ##ErrorLogSummary) a las cuentas de correo anteriormente mencionadas. La estructura del Job es:
set nocount on If Object_Id('tempdb..##ErrorLog') is Not Null Drop table ##ErrorLog If Object_Id('tempdb..##ErrorLogSummary') is Not Null Drop table ##ErrorLogSummary GO DECLARE @SummarizeResults as CHAR(1) DECLARE @RowCount_Saved as INT SET @SummarizeResults = 'N' DECLARE @dtLastRun datetime SELECT @dtLastRun = LastJobRun_date from rep.SQL_Overview_Last_Run_Date SELECT @dtLastRun = ISNULL (@dtLastRun ,DATEADD(dd,-1,current_timestamp))
PRINT 'Last Run Date = ' + Convert(char(26),@dtLastRun) CREATE TABLE ##ErrorLog( [Server] [nvarchar](128) NOT NULL, [dtMessage] [datetime] NULL, [SPID] [varchar](50) NULL, [vchMessage] [nvarchar](2048) NULL, [ID] [int] NULL ) ON [PRIMARY] -- Using the Last Run Date copy all valid error messages to Global Temp table INSERT INTO ##ErrorLog SELECT [Server] ,[dtMessage] ,[SPID] ,[vchMessage] ,[ID] FROM [SQL_Overview].[dbo].[ErrorLog] where ([vchMessage] like '%error%' or [vchMessage] like '%fail%' or [vchMessage] like '%Warning%' or [vchMessage] like '%The SQL Server cannot obtain a LOCK resource at this time%' or [vchMessage] like '%Autogrow of file%in database%cancelled or timed out after%' or [vchMessage] like '%Consider using ALTER DATABASE to set smaller FILEGROWTH%' or [vchMessage] like '% is full%' or [vchMessage] like '% blocking processes%' or [vchMessage] like '%SQL Server has encountered%IO requests taking longer%to complete%') and [vchMessage] not like '%\ERRORLOG%' and [vchMessage] not like '%Attempting to cycle errorlog%' and [vchMessage] not like '%Errorlog has been reinitialized.%' and [vchMessage] not like '%found 0 errors and repaired 0 errors.%' and [vchMessage] not like '%without errors%' and [vchMessage] not like '%This is an informational message%' and [vchMessage] not like '%WARNING:%Failed to reserve contiguous memory%' and [vchMessage] not like '%The error log has been reinitialized%' and [vchMessage] not like '%Setting database option ANSI_WARNINGS%' and [vchMessage] not like '%Error: 15457, Severity: 0, State: 1%' and [vchMessage] <> 'Error: 18456, Severity: 14, State: 16.' and [dtMessage] >= @dtLastRun SET @RowCount_Saved = @@ROWCOUNT SELECT 'Row Count = ', @RowCount_Saved -- Jobs failures found send e-mail IF @RowCount_Saved > 100 SET @SummarizeResults = 'Y' IF @SummarizeResults = 'Y' BEGIN CREATE TABLE ##ErrorLogSummary ( [Server] [nvarchar](128) NOT NULL, [dtMessage] [datetime] NULL, [SPID] [varchar](50) NULL, [vchMessage] [nvarchar](1024) NULL, [Cnt] [int] NULL ) ON [PRIMARY] INSERT INTO ##ErrorLogSummary SELECT [Server] ,MAX([dtMessage]) ,[SPID]
,[vchMessage] ,Count(*) FROM [SQL_Overview].[dbo].[ErrorLog] where ([vchMessage] like '%error%' or [vchMessage] like '%fail%' or [vchMessage] like '%Warning%' or [vchMessage] like '%The SQL Server cannot obtain a LOCK resource at this time%' or [vchMessage] like '%Autogrow of file%in database%cancelled or timed out after%' or [vchMessage] like '%Consider using ALTER DATABASE to set smaller FILEGROWTH%' or [vchMessage] like '% is full%' or [vchMessage] like '% blocking processes%' or [vchMessage] like '%SQL Server has encountered%IO requests taking longer%to complete%') and [vchMessage] not like '%\ERRORLOG%' and [vchMessage] not like '%found 0 errors and repaired 0 errors.%' and [vchMessage] not like '%without errors%' and [vchMessage] not like '%This is an informational message%' and [vchMessage] not like '%WARNING:%Failed to reserve contiguous memory%' and [vchMessage] not like '%The error log has been reinitialized%' and [vchMessage] not like '%Setting database option ANSI_WARNINGS%' and [vchMessage] not like '%Error: 15457, Severity: 0, State: 1%' and [vchMessage] <> 'Error: 18456, Severity: 14, State: 16.' and [dtMessage] >= @dtLastRun GROUP BY [Server],[SPID],[vchMessage] END -- Jobs failures found send e-mail IF @RowCount_Saved > 0 BEGIN -- Send E-Mail if new failed ErroLog Error and Warnings are Detected DECLARE @From varchar (100) DECLARE @Subject varchar (100) SELECT @From=@@SERVERNAME + '@[Link]' SELECT @Subject='SQL Server - ErrorLog Messages Requiring Further Review' DECLARE @TableHTML NVARCHAR(MAX) ; IF @SummarizeResults = 'Y' BEGIN SET @TableHTML = N'<table border="1">' + N'<tr>' + N'<th>Server</th>' + N'<th>dtMessage(MAX)</th>' + N'<th>SPID</th>' + N'<th>vchMessage</th>' + N'<th>Count</th>' + N'</tr>' + CAST ( ( SELECT td=[Server],'' ,td=[dtMessage],'' ,td=ISNULL([SPID],' '),'' ,td=[vchMessage],'' ,td=[Cnt] FROM ##ErrorLogSummary ORDER BY 1,2,3,4 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +
N'</table>' ; END ELSE BEGIN SET @TableHTML = N'<table border="1">' + N'<tr>' + N'<th>Server</th>' + N'<th>dtMessage</th>' + N'<th>SPID</th>' + N'<th>vchMessage</th>' + N'</tr>' + CAST ( ( SELECT td=[Server],'' ,td=[dtMessage],'' ,td=ISNULL([SPID],' '),'' ,td=[vchMessage] FROM ##ErrorLog ORDER BY 1,2,3,4 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; END /*Declara variables*/ declare @MailsEnviar as varchar(max) declare @i as int declare @tabla as table(i int identity(1,1),mail varchar(150)) /*Define valores de variables*/ insert into @tabla select mail from sql_overview.dbo.SQL_PersonasNotificacion where id_cliente=-1 set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/ WHILE @i <=(SELECT MAX(i) from @tabla) BEGIN if @i = 1 BEGIN set @MailsEnviar = (Select mail from @tabla where i = @i) END ELSE BEGIN set @MailsEnviar = @MailsEnviar + ';' + (Select mail from @tabla where i = @i) END set @i = @i+1 END EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar, @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; END If Object_Id('tempdb..##ErrorLog') is Not Null Drop table ##ErrorLog If Object_Id('tempdb..##ErrorLogSummary') is Not Null
Drop table ##ErrorLogSummary
9. Paso: Se ejecuta el Job Report Table Refresh Errors
EXEC [Link].sp_start_job N'DBA-SQL Overview - Report Table Refresh Errors'
En este job se enva el correo 'SQL Server - SQL Overview Table Refresh Errors' a los correos mencionados de acuerdo con la informacin obtenida de la tabla [SSIS_Errors]. La estructura del Job es:
SET nocount ON -DECLARE @Subject VARCHAR (100) SET @Subject='SQL Server - SQL Overview Table Refresh Errors' DECLARE @Count AS INT SELECT @Count=COUNT(*) FROM SSIS_Errors PRINT @Count IF @Count > 0 BEGIN DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<table border="1">' + N'<tr>' + N'<th>Server</th>' + N'<th>TaskName</th>' + N'<th>ErrorCode</th>' + N'<th>ErrorDescription</th>' + N'</tr>' + CAST ( ( SELECT td=[server],'' ,td=[TaskName],'' ,td=[ErrorCode],'' ,td=[ErrorDescription],'' FROM [SSIS_Errors] ORDER BY 1,2,3,4 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; /*Declara variables*/ declare @MailsEnviar as varchar(max) declare @i as int declare @tabla as table(i int identity(1,1),mail varchar(150)) /*Define valores de variables*/ insert into @tabla select mail from sql_overview.dbo.SQL_PersonasNotificacion where id_cliente=-1 set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/ WHILE @i <=(SELECT MAX(i) from @tabla)
BEGIN if @i = 1 BEGIN set @MailsEnviar = (Select mail from @tabla where i = @i) END ELSE BEGIN set @MailsEnviar = @MailsEnviar + ';' + (Select mail from @tabla where i = @i) END set @i = @i+1 END EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar, @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; END
10. Paso: Ejecuta el job Report Instances Recently Started
EXEC [Link].sp_start_job N'DBA-SQL Overview - Report Instances Recently Started'
Este job enva el correo 'SQL Server - Instances Recently Started' a las cuentas ya mencionadas de acuerdo a la comparacin del campo [InstanceLastStartDate] de la tabla [dbo].[SQL_Servers] (obtenida a travs del paquete Overview) si es mayor a la fecha contenida en la tabla [rep].SQL_Overview_Last_Run_Date. La estructura del Job es:
SET nocount ON -DECLARE @dtLastRun datetime SELECT @dtLastRun = LastJobRun_date FROM [rep].SQL_Overview_Last_Run_Date SELECT @dtLastRun = ISNULL (@dtLastRun ,DATEADD(dd,-2,CURRENT_TIMESTAMP)) PRINT 'Last Run Date = ' + CONVERT(CHAR(26),@dtLastRun) -DECLARE @Subject VARCHAR (100) SET @Subject='SQL Server - Instances Recently Started' DECLARE @Count AS INT SELECT @Count=COUNT(*) FROM [dbo].[SQL_Servers] WHERE [InstanceLastStartDate] > @dtLastRun PRINT @Count IF @Count > 0 BEGIN DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<table border="1">' + N'<tr>' + N'<th>Server</th>' + N'<th>InstanceLastStartDate</th>' + N'<th>ProductVersion</th>' +
N'<th>WindowsVersion</th>' + N'<th>ProcessorCount</th>' + N'<th>PhysicalMemory</th>' + N'</tr>' + CAST ( ( SELECT td=[Server],'' ,td=[InstanceLastStartDate],'' ,td=[ProductVersion],'' ,td=[WindowsVersion],'' ,td=[ProcessorCount],'' ,td=[PhysicalMemory],'' FROM [dbo].[SQL_Servers] WHERE [InstanceLastStartDate] > @dtLastRun ORDER BY 1,2 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; /*Declara variables*/ declare @MailsEnviar as varchar(max) declare @i as int declare @tabla as table(i int identity(1,1),mail varchar(150)) /*Define valores de variables*/ insert into @tabla select mail from sql_overview.dbo.SQL_PersonasNotificacion where id_cliente=-1 set @MailsEnviar = '' set @i = 1 /*Arma la cadena de mails para enviar*/ WHILE @i <=(SELECT MAX(i) from @tabla) BEGIN if @i = 1 BEGIN set @MailsEnviar = (Select mail from @tabla where i = @i) END ELSE BEGIN set @MailsEnviar = @MailsEnviar + ';' + (Select mail from @tabla where i = @i) END set @i = @i+1 END EXEC [Link].sp_send_dbmail @profile_name = 'SQLMailCAS', @recipients = @MailsEnviar, @subject = @Subject, @body = @tableHTML, @body_format = 'HTML' ; END
11. Paso: