September 21, 2008 at 2:00 am
Hello,
We need to get error log in email when a production database failed. Say if there are 6 databases and only 1 database is failed, we need to get message like this particular database is failed with some kind of error log.
I attached the procedures in attachments..
Here is the whole scenario.
1. We are using SP_DB_BACKUP procedure to backup the databases.
2.A Job is created using the above procedure as step1.
3.If step1 fails it goes to step2 which has 'Failure_Notification' procedure like FAILURE_NOTIFICATION 'Production USER DB Backup Failed on ABC'
4.If step1 success goes to step3 which is 'success_Notification' procedure.
So, Now we are getting the only message 'Production USER DB Backup Failed on ABC' in email as subject, with no body.
So we want like some more information in the body like which database failed, when it failed and some error log which would give some more information to diagnose the error.
Thank You
September 22, 2008 at 2:35 am
create table #error_status
(
logdate datetime,Processinfo varchar(20),text nvarchar(4000))
insert into #error_status
exec sp_readerrorlog
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notification@express.com',
@recipients = 'bkumar@express.com',
@query = 'select top 10 * from #error_status where text like ''%BACKUP failed%'' order by 1 desc' ,
@subject = 'Error Log Status',
@attach_query_result_as_file = 1 ;
-- @profile_name: profile_name must be the name of an existing Database Mail profile
drop table #error_status
--------------------------------------------------------------------------------------
this is the script which uses database mail .....so #error_status table which contains error list from eventlog
the mail containning error list which will be sent
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 22, 2008 at 10:38 am
Thank You bhuvnesh,
Where exactly I need to put this script. like as a job step or inside the procedure Failure_Notification'
What Iam guessing is I need to add the script created by you in the below procedure. But where exactly I need to add? Correct me if Iam wrong?
CREATE PROCEDURE FAILURE_NOTIFICATION
@Subject varchar(100)=" ",
@Body varchar(500) =" "
AS
BEGIN
--uses below stored proc to send out failure notification to the dba team and group
EXEC SP_SEND_CDOSYSMAIL
'SQLSERVER@ABC.COM',
'MADHU@ABC.COM,DBAGROUP@ABC.COM',
'STEVE@ABC.COM,SPAUL@ABC.COM,HARY@ABC.COM,',
@Subject,
@Body
END
GO
Could you please see my attachments and clarify me the steps to do.
September 22, 2008 at 2:57 pm
Hello bhuvnesh,
could you plz help me out..it a bit urjent
Thanks
September 22, 2008 at 9:49 pm
Hi,
1.Modify sp_send_cdosysmail proc with my script
2. call above SP in FAILURE_NOTIFICATION proc .
And sorry I couldn’t reply you yesterday as I left at 6 30 IST 🙂
And dont forget to truncate the temp table .
you can schedule this mail after the backup taken.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 23, 2008 at 12:29 am
Hello bhuvnesh,
I know its too frustating to ask the same question several times...
sorry to again ask you the same questions. I came in the middle of the project. The previous DBA used EXEC SP_SEND_CDOSYSMAIL procedure to send emails instead of sqlmail due to lackof outlook or exchange.
what should I give in place of @profile_name: profile_name must be the name of an existing
Database Mail profile.
what I understand is add your script in the below procedure
CREATE PROCEDURE FAILURE_NOTIFICATION
@Subject varchar(100)=" ",
@Body varchar(500) =" "
AS
BEGIN
--uses below stored proc to send out failure notification to the dba team and group
EXEC SP_SEND_CDOSYSMAIL
'SQLSERVER@ABC.COM',
'MADHU@ABC.COM,DBAGROUP@ABC.COM',
'SMITH@ABC.COM,PAUL@ABC.COM,STEVE@ABC.COM,',
@Subject,
@Body
END
GO
could you plz tell me where exactky I need to add your script in above procedure?
and you mentioned that dont forget to truncate the temp table .
you can schedule this mail after the backup taken. I did not understand this properly.
please tell me the about the above mentioned 'dont forget to truncate the temp table .
you can schedule this mail after the backup taken'.
It would be great if put this scenario in step -by-step?
Many thanks
Madhu
September 23, 2008 at 12:48 am
Madhu,
Follow the 3 steps sequencially and forget about JOB.:) where ever you calling
FAILURE_NOTIFICATION ,dont pass parameters
step 1 :
create table #error_status
(
logdate datetime,Processinfo varchar(20),text nvarchar(4000))
-----------------------------------------------------
step 2 :
CREATE PROCEDURE [dbo].[sp_send_cdosysmail] @From varchar(100) , @To varchar(100)
as
insert into #error_status
exec sp_readerrorlog
GO
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @From,
@recipients = @To,
@query = 'select top 10 * from #error_status where text like ''%BACKUP failed%'' order by 1 desc' ,
@subject = 'Error Log Status',
@attach_query_result_as_file = 1 ;
GO
truncate table #error_status
----------------------------------------------------------------
step 2 :
CREATE PROCEDURE FAILURE_NOTIFICATION
AS
BEGIN
EXEC SP_SEND_CDOSYSMAIL
'SQLSERVER@ABC.COM',
'GSCOTT@ABC.COM;DBAGROUP@ABC.COM'
END
GO
-----------------------------------------------------------------------
By the ways in which company u r working and r u DBA ???
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 23, 2008 at 10:36 am
hi,
The stored procedure 'msdb.dbo.sp_send_dbmail' is not exist in sql server 2000? So which procedure can we use instaed of that?
Thanks
September 23, 2008 at 10:18 pm
oh.....this is a fall 🙁 in sql 2000 we have to go with
master.dbo.xp_sendmail
but sorry no idea about that
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 24, 2008 at 10:57 am
Hi,
We are using the following method to send emails.
http://support.microsoft.com/kb/312839
In that CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
So in the above parameter @body varchar(4000) what ever message we give that will come in the email.
So my question can we pass any query out put in @body parameter which will send errorlog to the email.
September 25, 2008 at 12:53 pm
Hi,
Iam able to get some error message in email when the job succeeded/failed like
'The job succeeded. The Job was invoked by User ABC\sqladmin. The last step to run was step 2(Success_Notification). The job was requested to start at step 1 (Daily Backup).' in the body of email.
by adding the below script.. So my question is the job id for paricular job is unique and its remains same all the time? and can we make this script some more meaningful?
CREATE PROCEDURE FAILURE_NOTIFICATION
@Subject varchar(100),
@jobid VARCHAR(100)
AS
BEGIN
declaRe @body varchar(8000)
declare @msg varchar(500)
select @msg= message from msdb..sysjobhistory
where job_id= @jobid
and run_date=cast(convert(varchar(12),getdate(),112) AS INT)--run_date='20080923'
set @body = @msg
--uses below stored proc to send out failure notification to the dba team and group
EXEC SP_SEND_CDOSYSMAIL
'SQLSERVER@ABC.COM',
'ABC@ABC.COM,ABC@ABC.COM',
'ABC@ABC.COM,ABC@ABC.COM,ABC@ABC.COM,',
@Subject,
@BODY
END
go
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply