SQL - find problems - Standard solutions to random problems

Specialized Performance Troubleshooting (Part 1: How to troubleshoot Forwarded Records)

http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/04/specialized-performance-troubleshooting-part-1-how-to-troubleshoot-forwarded-records.aspx

Specialized Performance Troubleshooting (Part 2: How to troubleshoot Memory problems in SQL Server)

http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/11/specialized-performance-troubleshooting-part-2-how-to-troubleshoot-memory-problems-in-sql-server.aspx

Specialized Performance Troubleshooting (Part 3: How to identify storage issues at a SQL Server box)

http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/18/specialized-performance-troubleshooting-part-3-how-to-identify-storage-issues-at-a-sql-server-box.aspx

How to troubleshoot SQL Server performance issues with simple tools (Part 1: How to collect a detailed Perfmon trace)

http://blogs.msdn.com/b/john_daskalakis/archive/2013/10/07/how-to-troubleshoot-sql-server-performance-issues-with-simple-tools-part-1-how-to-collect-a-detailed-perfmon-trace.aspx

http://blogs.msdn.com/b/john_daskalakis/

proc sp_alert_failed_jobs

/*
exec sp_alert_failed_jobs @pastdays = -3 , @exludejob ='''exportbill_dts'',''daily_update_erptables'''
*/
alter  proc sp_alert_failed_jobs
(
    @pastdays int = -1,
    @exludejob varchar(1000) =''
)

as
begin

--declare @pastdays int = -1
--select @exludejob
if(object_id('msdb..__tmp_failed_jobs')>1)
    drop table __tmp_failed_jobs
create table __tmp_failed_jobs (name varchar(1000), run_date int, run_time int, step_id int, step_name varchar(1000), message ntext)

declare @sql nvarchar(max)

set @sql = 'select j.name,h.run_date,h.run_time,h.step_id, h.step_name, h.message
From msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
where h.run_status = 0
and h.run_date >= convert(int, CONVERT(varchar, dateadd(dd,'+convert(varchar,@pastdays)+',GETDATE()),112))
and j.name not in ('+@exludejob+')
order by h.run_date desc, h.step_id asc'

--print @sql
insert into __tmp_failed_jobs exec sp_executesql @sql

--select * From __tmp_failed_jobs

if(select COUNT(*) from __tmp_failed_jobs) >0
begin
    declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
    set @subject = 'Failed Jobs - SQL ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
    set @recipientslist = 'patrickalexander@hotmail.com;ACheckDB@acheckamerica.com;kcinty@acheckamerica.com;qkong@acheckamerica.com'
   
    DECLARE @tableHTML  NVARCHAR(MAX) , @subjectstring nvarchar(200);

    SET @tableHTML =
        N'

Failed jobs on '+@@SERVERNAME+'

' +
        N'' +
        N' ' +
        N'' +
        N'' +
        N'< ' +
        CAST ( ( SELECT td = name,       '',
                        td = convert(varchar,run_date),       '',
                        td = convert(varchar,run_time), '',
                        td = step_id, '',
                        td = step_name, '',
                        td = message
                  FROM __tmp_failed_jobs
                  FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX) ) +
        N'

Job Name	Date	Time	StepID	StepName	Message

' ;
       
        --print @tableHTML

        EXEC msdb.dbo.sp_send_dbmail
            --@profile_name = 'default',
            @recipients =@recipientslist,
            @body = @tableHTML,
            @body_format = 'HTML' ,
            @subject = @subject;       
end

end