this script send an HTML email every day with information about failed jobs . the script use the persist.mail to send the report, but you can use another mail component with support html.
/* procedimiento para el informe diario de qué jobs y en qúe pasos han fallado*/CREATE PROCEDURE job_history
AS
declare @cuerpomail as nvarchar(4000)
declare @tablamail as nvarchar(4000)
declare @nombrejob as nvarchar(500)
declare @server as nvarchar(500)
declare @jobid as nvarchar(500)
declare @status as nvarchar(100)
declare @fecha as nvarchar(50)
declare @paso as nvarchar(100)
declare @mensaje as nvarchar(500)
declare @pasoid as nvarchar(10)
declare @fechahoy as smalldatetime
declare @anno as nvarchar(10)
declare @mes as nvarchar(10)
declare @dia as nvarchar(10)
declare @registro as nvarchar(100)
--variables para mail
declare @sujetomail as nvarchar(400)
declare @hr int
declare @mail int
---------------------------------
/**********************************************************************************//*obtengo la fecha y la descompongo para pasarla como 'yyyymmdd' *//**********************************************************************************/
set @fechahoy=getdate()
set @anno=year(@fechahoy)
set @mes=month(@fechahoy)
set @dia=day(@fechahoy)
if len(@dia)=1
begin
set @dia='0'+@dia
end
if len(@mes)=1
begin
set @mes='0'+@mes
end
print 'año actual: ' +@anno
print 'mes actual: '+@mes
print 'dia actual: '+@dia
/*******************************************************************************/
set @registro=''
set @tablamail='<table align=center border=1 width=80% bordercolor=black>'
set @tablamail=@tablamail+'<tr><td>'
set @tablamail=@tablamail+'<tr bgcolor=white><td align=center colspan=3><img src="cid:sqlserver" align="absolutemiddle"> <b>Reporte '+cast(getdate() as nvarchar(50))+'</b></td></tr>'
declare Cenvio cursor local for
SELECT J.name,JH.server,JH.job_id,
CASE run_status
WHEN 0 THEN 'fallado'
WHEN 1 THEN 'Terminado'
ELSE '???'
END as run_status, run_date, step_id, step_name, message
FROM msdb.dbo.sysjobhistory JH
inner join msdb.dbo.sysjobs J ON JH.job_id = J.job_id
where run_status=0 and run_date=@anno+@mes+@dia
group by J.name,JH.job_id, JH.run_date, JH.server,JH.run_status , JH.step_id, JH.step_name,JH.message
order by run_date desc
open Cenvio
fetch next from Cenvio into @nombrejob,@server,@jobid,@status,@fecha,@pasoid,@paso,@mensaje
while @@fetch_status=0
begin
if @nombrejob<>@registro
begin
if @pasoid='0'
begin
print @nombrejob
end
else
begin
print @nombrejob+'---('+@pasoid+')'+@paso
end
set @tablamail=@tablamail+'<tr bordercolor=#FBEDBB><td colspan=2 bgcolor=#ffe4b5><img src="cid:failed" > '
set @tablamail=@tablamail+'<font color=red><b>'+@nombrejob+'</font></b> '
set @tablamail=@tablamail+'</td></tr>'
end
else
begin
if @pasoid='0'
begin
print '---'
end
else
begin
print '---------------------------('+@pasoid+')'+@paso
end
set @tablamail=@tablamail+'<tr bordercolor=#FBEDBB valign="top"><td width="40%">'
set @tablamail=@tablamail+'<font size=-1>Paso ('+@pasoid+') <br>'+@paso+'</font> '
set @tablamail=@tablamail+'</td>'
set @tablamail=@tablamail+'<td>'
set @tablamail=@tablamail+'<font size=-2>'+@mensaje+'</font></td></tr>'
end
set @registro=@nombrejob
fetch next from Cenvio into @nombrejob,@server,@jobid,@status,@fecha,@pasoid,@paso,@mensaje
end
close Cenvio
deallocate Cenvio
set @tablamail=@tablamail+'</table>'
set @cuerpomail='<HTML><BODY bgcolor=lightgoldenrodyellow><center><b><img src="cid:logo" alt="Misco Online"></b><br></center>'
set @cuerpomail=@cuerpomail+@tablamail
exec @hr = sp_OACreate 'Persits.MailSender', @Mail OUT
exec @hr=sp_OAsetproperty @Mail,'host','xxx.xxx.xxx.xxx'
exec @hr=sp_OASetProperty @Mail, 'fromName','XXXXXX'
exec @hr = sp_OASetProperty @Mail, 'Subject', '(JOB) Reporte JOBS FALLIDOS'
exec @hr=sp_OAsetproperty @mail,'from','xxx@xxx.com'
exec @hr=sp_OAsetproperty @Mail,'ishtml','1'
exec @hr = sp_OAMethod @Mail, 'AddBcc', NULL, 'xxx@xxx.com'
exec @hr=sp_OAmethod @mail, 'AddEmbeddedImage',null,'d:\InetPub\Intranet\images\LogoMiscoIntranet.jpg','logo'
exec @hr=sp_OAmethod @mail, 'AddEmbeddedImage',null,'d:\InetPub\Intranet\images\failed.gif','failed'
exec @hr=sp_OAmethod @mail, 'AddEmbeddedImage',null,'d:\InetPub\Intranet\images\sqlserver.jpg','sqlserver'
exec @hr=sp_OASetProperty @mail,'body',@cuerpomail
exec @hr = sp_OAMethod @Mail, 'Send'
GO 
When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.
2018-05-18 (first published: 2015-10-19)
4,855 reads
If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.
2011-08-18
5,492 reads
Far too often, people who are not trained database administrators somehow end up responsible for a database. They lack the proper training and knowledge to maintain their database, and problems begin to develop. Here’s a primer for all those involuntary DBAs who need a crash course in database maintenance best practices.
2008-08-13
6,696 reads
This script will change the owner of the stored procedure to dbo. It must be run in the same database as the stored procedures.
2001-10-28
1,599 reads
Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled. SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled') There are a number of other useful […]
2001-10-28
1,271 reads