April 1, 2009 at 3:19 am
Dear Experts,
I wish to run the "Transaction Log Shipping Status Report" on my secondary server at selected intervals and have it email the report to recipients. I want this to be run in job, when the backup / copy / restore job is succeeded and then this report must be send to my mail id.
Fast replies are appreciated.....
Thanks,
CH&HU.
April 1, 2009 at 4:41 am
Hi,
You can add a step to each of those transaction log jobs which get created from the logshipping wizard. In that step you can add the send mail details based on success or failure criteria as given below. Each time the job succeeds or fails it will send out the mail. Use SQL Mail for SQL 2000 & DB MAil for 2005
use master
go
EXEC xp_sendmail @recipients = 'robertk',
@subject = 'SQL Server Report',
@message = 'The backup succeeded'
go
Have a nice day !!!
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
April 1, 2009 at 4:49 am
Thanks for the reply,
It only shows the process succeed or failed. I have already done in the past and working on that. My wish that to mail a status report as,
Logshipping backup / copy / restore status reports.
Rgds,
CH&HU.
April 1, 2009 at 6:03 am
Can anyone please look into this ASAP.
And help me out in making this possible.............
April 1, 2009 at 9:47 am
Not quite clear about what Status you are interested in.
Maybe you could try these:
ON YOUR Secondary Server:
--Get the latest copy and restore job status--
select * from(
select top 6 j.name as Jobname,jh.message, run_status, run_date, Run_time
from msdb.dbo.sysjobhistory jh
join
msdb..sysjobs j on j.job_id=jh.job_id
where j.name like '%LSCOPY%'
order by instance_id desc)a
UNION ALL
SELECT * FROM
(select top 8 j.name as Jobname,jh.message, run_status, run_date, Run_time
from msdb.dbo.sysjobhistory jh
join
msdb..sysjobs j on j.job_id=jh.job_id
where j.name like '%LSrestore%'
order by instance_id desc)b
On your primary server:
--Get the latest backup job status--
select top 7 j.name as Jobname,jh.message, run_status, run_date, Run_time
from msdb.dbo.sysjobhistory jh
join
msdb..sysjobs j
on j.job_id=jh.job_id
where j.name like '%LSBACKUP%'
order by instance_id desc
Basically everything you want to know about the job running status are in the sysjobhistory table, you can modify the above scripts to fit your requirements, e.g your jobname maybe different..or you may want to get more details other than the above provided.
Once you got the scripts fixed, you can put them into DBMAIL using @query. Set up a seperate job for emailing..schedule it a bit later than your LS jobs schedule. then you should get the status reports in your email.
April 1, 2009 at 10:23 pm
Thanks alot Dear for taking time to respond,
I tried this and working fine to result. Other than this i tried the queries which stored in MSDB they are working fine to provide results, but the result and the report must be mailed. Thats what my intention is to make sure.
AM unable to get that how can this result can be sent to mail id as a .pdf / text / excel etc....
AM blank now... the way am thinking abt the issues.....
Could you please help me that haw can this result be dropped to my mail when the job got succeeded.
FAST...
Thanks in advance,
CH&HU.
April 2, 2009 at 3:00 am
exec msdb..sp_send_dbmail @recipients='youmail@name.com'
, @subject ='your suject goes here'
, @QUERY = 'select top 7 j.name as Jobname,jh.message, run_status, run_date, Run_time
from msdb.dbo.sysjobhistory jh
join
msdb..sysjobs j
on j.job_id=jh.job_id
where j.name like ''%LSBACKUP%''
order by instance_id desc'
Run this in a job.. I am using same code what already suggested to you
April 2, 2009 at 3:02 am
and the only difference is for each single quote (') in query, you have to use two single quotes('')
April 2, 2009 at 3:40 am
Hi,
After running your query i got the below error :
Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 107
No global profile is configured. Specify a profile name in the @profile_name parameter.
How can i make cleared...?
Please let me know if i have missed anything....
April 2, 2009 at 3:45 am
check your dbmail setup, you should have one profile set to default .. if not you can use
@profile_name = 'valid profile name'
in sp_send_dbmail statement. (check BOL for sp_send_dbmail SP)
April 2, 2009 at 4:01 am
Hi,
Am running the below query as it is -
-----------------------------------------------------------------------
exec msdb..sp_send_dbmail
@profile_name = 'Chandhra_local',@recipients='cshekhar@agilitylogistics.com'
, @subject ='Log Shipping Backup Job succeeded'
, @QUERY = ''select top 7 j.name as Jobname,jh.message, run_status, run_date, Run_time
from msdb.dbo.sysjobhistory jh
join
msdb..sysjobs j
on j.job_id=jh.job_id
where j.name like '%LSBACKUP%'
order by instance_id desc
--------------------------------------------------------------------------------
But the result i can get to the mail is nothing.... the body of the mail is blank......... and the result for the query on manula run is :
LSBackup_eCOEApplDBThe job succeeded. The Job was invoked by Schedule 41 (LSBackupSchedule_INHYDSHEKAR\DBA_AJIRA1). The last step to run was step 1 (Log shipping backup log job step.).120090402150000
LSBackup_eCOEApplDBExecuted as user: INHYDSHEKAR\SYSTEM. The step succeeded.120090402150000
LSBackup_eCOEApplDB2009-04-02 15:00:02.27 ----- END OF TRANSACTION LOG BACKUP ----- Exit Status: 0 (Success) 420090402150000
LSBackup_eCOEApplDB2009-04-02 15:00:02.26 Deleting old log backup files. Primary Database: 'eCOEApplDB' 2009-04-02 15:00:02.26 The backup operation was successful. Primary Database: 'eCOEApplDB', Log Backup File: 'E:\LS\BACKUP\eCOEApplDB_20090402093001.trn' 420090402150000
LSBackup_eCOEApplDB2009-04-02 15:00:01.68 Backing up transaction log. Primary Database: 'eCOEApplDB', Log Backup File: 'E:\LS\BACKUP\eCOEApplDB_20090402093001.trn' 420090402150000
LSBackup_eCOEApplDB2009-04-02 15:00:01.43 Starting transaction log backup. Primary ID: '74bca141-132c-45de-9de8-186dcd95b07d' 2009-04-02 15:00:01.45 Retrieving backup settings. Primary ID: '74bca141-132c-45de-9de8-186dcd95b07d' 2009-04-02 15:00:01.46 Retrieved backup settings. Primary Database: 'eCOEApplDB', Backup Directory: 'E:\LS\BACKUP\', Backup Retention Period: 4320 minute(s) 420090402150000
LSBackup_eCOEApplDB2009-04-02 15:00:01.16 ----- START OF TRANSACTION LOG BACKUP ----- 420090402150000
AM unable to understand why the result set is not coming to mail...... Was that the same yu have explained me.... will the result set come to mail or not ?
Please clarify me if am wrong understood in any other area.
Thanks.
April 2, 2009 at 4:17 am
syntax is wrong, use following
exec msdb..sp_send_dbmail
@profile_name = 'Chandhra_local',@recipients='cshekhar@agilitylogistics.com'
, @subject ='Log Shipping Backup Job succeeded'
, @QUERY = 'select top 7 j.name as Jobname,jh.message, run_status, run_date, Run_time
from msdb.dbo.sysjobhistory jh
join
msdb..sysjobs j
on j.job_id=jh.job_id
where j.name like ''%LSBACKUP%''
order by instance_id desc'
AS IT IS !!
April 2, 2009 at 4:25 am
Excellent....... Its working like anything.......
Thanks a LOT for you rfast help......
Chill & Cheers,
CH&HU...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy