May 15, 2015 at 5:27 am
hello,
i am working on logic for a restore job and want to send errors to myself when they fail. the issues is that when it fails with something like low disk space i want to get the whole error message and not just the last statement (RESTORE DATABASE is terminating abnormally)....which is all i get when using the following:
begin try
query here
end try
begin catch
declare @error varchar(1000)
set @error = error_message()
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail',
@recipients = 'samuel@xxxxxxxxxxx.com',
@body = @error,
@subject = 'Restore Step Failure' ;
end catch
I want to get this in the body:
Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume 'C:\' to create the database. The database requires 419839344640 additional free bytes, while only 190869860352 bytes are available.
Msg 3119, Level 16, State 4, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
is there any way to do this or will i have to create individual alerts based on the error numbers?
thanks is advance
samuel
May 15, 2015 at 8:59 am
If it is a SQL Server job you can set an email notification in the job setup to send an email to an operator. It will contain the full error when the job fails.
May 15, 2015 at 9:02 am
the current logic, to avoid individual jobs per database, is to go to the next database restore if the current one fails.
this always results in the job showing success but with a failed step in the middle.
i need to email from an individual job step with the specific step error and not the overall job exit error.
thanks though
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply