How can i subscribe "Transaction Log Shipping Status Report" to my mail through JOB.

  • 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.

  • 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":-) **

  • 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.

  • Can anyone please look into this ASAP.

    And help me out in making this possible............. 🙁

  • 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.

  • 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.

  • 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

  • and the only difference is for each single quote (') in query, you have to use two single quotes('')

  • 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....

  • 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)

  • 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.

  • 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 !!

  • 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