1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| USE [Administrator]
GO
/****** Object: Trigger [DiskSpace].[low_disk_space_notification] Script Date: 08/25/2010 11:38:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 13 Aout 2010
-- Description: Envoie un email lorsqu'un drive est en bas de 20% d'espace libre
-- =============================================
ALTER TRIGGER [DiskSpace].[low_disk_space_notification]
on [DiskSpace].[FreeSpace]
after insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if (select disksize from inserted where percentage < 20) > 0
BEGIN
declare @computer as varchar(128)
declare @drive as varchar(2)
declare @drivename as varchar(15)
declare @percentage as decimal(10,5)
declare @message as varchar(max)
select @computer = computer, @drive = drive, @drivename = drivename, @percentage = percentage
from inserted
where percentage < 20
set @message = 'L''espace disque devient critique pour le serveur ' + @computer + ' sur le drive ' + @drive + ' ( ' + @drivename + ' )
avec un pourcentage de non utilisation de ' + cast(@percentage as varchar(15)) + ' pourcent. FAITES QUELQUES CHOSE !!!!!'
EXEC msdb..sp_send_dbmail @profile_name = 'ClusterMailProfile',
@recipients='usager@compagnie.com',
@subject='!! Espace Disque Critique sur serveur SQL !!',
@body=@message
END
END |
Partager