et envoie un mail si de tels processus sont trouvés

IF EXISTS (SELECT 1 FROM master.dbo.sysprocesses WITH (readuncommitted)
WHERE blocked > 0 AND waittime > 60000)
BEGIN
        DECLARE @strSQL varchar(2000)
 
        SET @strSQL = '
        select
        blocked,
        LEFT(rtrim(loginame) + '' - '' + rtrim(hostname), 30) as login,
        LEFT(rtrim(program_name), 20) as program,
        kpid,
        spid
        from master.dbo.sysprocesses with (readuncommitted)
        where kpid > 0
        order by kpid'
 
        exec master.dbo.xp_sendmail
        @recipients = 'dba',
        @message    = 'somes processes are blocked',
        @query      = @strSQL,
        @subject    = 'WARNING : blocked processes',
        --@attach_results = 'true',
        --@separator  = ',',
        @dbuse      = 'master'
END

envoie un mail lorsque le log de transaction de tempdb atteint une certaine taille

SET nocount ON
 
declare @logspaceused int, @logsize int
 
CREATE TABLE #junk( a varchar(30), logsize float, logspaceused float, status
int )
INSERT #junk
exec( 'DBCC sqlperf(logspace)' )
 
SELECT
@logsize = round(logsize, 0),
@logspaceused = round(logspaceused, 0)
FROM #junk where a = 'tempdb'
DROP TABLE #junk
 
IF ( (@logsize > 500) AND (@logspaceused > 70) ) begin
        declare @msg varchar(4000)
 
        SET @msg = 'the tempdb translog is now ' + cast(@logsize AS varchar) + ' mb. Find below a list of
        open transactions if any (result of
        DBCC OPENTRAN (''tempdb'') WITH TABLERESULTS, NO_INFOMSGS
        ).
        an open transaction can be identified (by obtaining the system process ID from
        the sp_who output) and terminated, if necessary.' + Char(013) + replicate('-',50) + Char(013)
 
        EXEC master.dbo.xp_sendmail
        @recipients = 'dba'
        @subject = 'warning : tempdb transaction log growing',
        @message = @msg,
        @query = 'DBCC OPENTRAN (''tempdb'') WITH TABLERESULTS, NO_INFOMSGS',
        @dbuse = 'tempdb'
end

par email. On donne simplement à la sp l'option startup

ALTER PROC dbo.sp_NotifyStart
AS BEGIN
        DECLARE @subj varchar(1000)
        DECLARE @computername char (16)
        EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
        ,'SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName',
        'ComputerName',@ComputerName   OUTPUT
 
        SET @subj = 'Service started : ' + @@SERVERNAME + ' - Node ' +
        @Computername
 
        EXECUTE master.dbo.xp_sendmail
        @recipients = 'gvamssql_dba@mscgva.ch',
        @subject    = @subj,
        @message    = 'The SQL Server service was started on the cluster
        node - probably a failover occured'
END
GO
 
EXEC sp_procoption 'sp_NotifyStart', 'startup', 'true'
GO