October 3, 2012 at 5:24 am
I just implemented a db mirroring configuration and log shipping configuration. All is working, what i want to do now is create a execute t-sql task in a maintenance plan so that an alert is send to an operator when db mirroring and log shipping fails. Can anybody help me please?
This is how far i got:
for db mirroring:
SELECT d.name
FROM sys.database_mirroring m
INNER JOIN sys.databases d
ON m.database_id = d.database_id
WHERE m.mirroring_state <> 4
something should go here and then
BEGIN
EXECUTE msdb.dbo.sp_notify_operator
@NAME=N'',
@SUBJECT=N'Mirror Server\Instance..',
@BODY=N'The mirroring of one of the databases on the databaseserver, instance is failing'
EXECUTE msdb.dbo.sp_notify_operator
@NAME=N'',@SUBJECT=N'Mirror ',
@BODY=N'The database mirroring to .. is failing. Please contact the database administrator!'
END
pff i have no clue...rly lost help is appreciated...
for log shipping i got:
use master
select*
frommsdb.dbo.sysjobs sj
inner joinmsdb.dbo.sysjobhistory sjh
on sj.job_id = sjh.job_id
wherestep_id = 0
andsj.name like 'LSRestore%'
andsjh.run_time > SOME CAST TIME??
andsjh.run_status <> 1
andsj.enabled = 1
.... SOMETHING HERE??
BEGIN
EXECUTE msdb.dbo.sp_notify_operator @NAME=N'',@SUBJECT=N'Transaction Log Shipping ',@BODY=N'The transaction log shipping of one of the databases on the ...instance is failing'
EXECUTE msdb.dbo.sp_notify_operator @NAME=N'',@SUBJECT=N'Transaction Log Shipping ',@BODY=N'The transaction log shipping to ...is failing. Please contact the database administrator!'
END
is there any article or something of a tutorial out there for this?
October 3, 2012 at 6:53 am
You only need to create an Alert with notify operator:
http://anyrest.wordpress.com/2010/11/25/create-alerts-for-sql-server-mirroring-failovers/
Or if you want to use a SQL Agent Job:
Alerts are designed for this situation though.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply