February 29, 2012 at 2:04 am
i'm writing below this code..
exec msdb..sp_send_dbmail
@profile_name = 'local',@recipients='chintu@www.com'
, @subject ='Log Shipping Job succeeded'
, @QUERY = 'exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N'
I want Log shipping Status Through Mail.please Suggest Me.....because last back up transaction in the day and last restoration trn file in the day...
so may be above script right..please help me.
SQL server DBA
February 29, 2012 at 4:26 am
Not sure why you'd want to use this (as the results from the sp_help_log_shipping_monitor will be in a horrible format using this method) but this will work:
exec msdb..sp_send_dbmail
@recipients='chintu@www.com'
, @subject ='Log Shipping Job succeeded'
, @QUERY = 'exec sp_help_log_shipping_monitor'
Which version of MSSQL are you running? Is there any reason you're not using the built-in report for logshipping (attached?) - it's a built in SSRS report that you could prolly easily schedule to have emailed to you via the Reporting Services Reporting Manager...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 1, 2012 at 2:20 am
Thankful...
my current Version is 2008r2 standard edition
but i don't know that process via Reporting services.i want last backup TRN in the day and last restoration TRN in the day.i know that only, am creating a one job and i can scheduled that job.please explain to me Via Reporting services via Email..please Help me
....
iam writing below Script,
exec msdb..sp_send_dbmail
@recipients='chintu@www.com'
, @subject ='Log Shipping Job succeeded'
, @QUERY = 'exec sp_help_log_shipping_monitor'
it shows the last backup and last copied and last restoration .but TimeStamp is not their..
I will definitely appreciate any help from your side.
SQL server DBA
March 1, 2012 at 2:34 am
I think Are You Said,
Reporting services configuration Manager To via Email Right.
But is it possible??
SQL server DBA
March 1, 2012 at 5:40 am
I'll get back with you shortly with an example
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 1, 2012 at 10:53 am
please Help me..its very Urgent..........
I Want last backUp TRN and Last restore TRN file in the day i., logshipping databases send via email also..(TRansactional stauts report through send email)
SQL server DBA
March 1, 2012 at 2:44 pm
Doesn't the query I posted above give you the results you are looking for?
I don't believe there is a way to access/schedule the SSRS report that's built in to MSSQL (if there is I can't find it).
Here's the dissected code from the exec sp_help_log_shipping_monitor if you run this on the log shipped server it will email you an email like the one attached. This code should run without change on any server (as long as database mail is enabled) - It will LOOP thorugh all the log shipped databases, returning the log shipping status for EACH
USE [master]
GO
begin
set nocount on
DECLARE @Recipientsvarchar(275)
DECLARE @Subject varchar(275)
DECLARE @Body varchar(MAX)
DECLARE @Server varchar(25)
DECLARE @idx int, @status varchar(25), @DB varchar(25), @LastCopy varchar(6), @LastCFile varchar(250),
@LastRestore varchar(6), @LastRFile varchar(250), @Latency varchar(3), @Threshold varchar(3)
declare @retcode int
,@primary_id uniqueidentifier
,@primary_server sysname
,@primary_database sysname
,@backup_threshold int
,@is_backup_alert_enabled bit
,@secondary_id uniqueidentifier
,@secondary_server sysname
,@secondary_database sysname
,@restore_threshold int
,@is_restore_alert_enabled bit
,@last_copied_file nvarchar(500)
,@last_copied_utc datetime
,@time_since_last_copy int
,@last_restored_file nvarchar(500)
,@last_restored_utc datetime
,@time_since_last_restore int
,@last_restored_latency int
,@prev_primary_server sysname
,@prev_primary_database sysname
,@monitor_server sysname
,@monitor_server_security_mode int
,@is_monitor_local bit
,@curutcdate datetime
,@linkcmd nvarchar(4000)
SET @Recipients = 'you@company.com'
SET @Body = ''
SET @Server = @@SERVERNAME
SET @Subject = @Server + ' :: Daily Transaction Log Shipping Status'
SET @Body = '<p style="font-size:12px;font-family:Verdana"><b>' + @Server + ': </b></font><hr style="width: 100%; height: 1px;"> '
create table #log_shipping_monitor
(
idx int identity(1,1)
,status bit null
,is_primary bit not null default 0
,server sysname
,database_name sysname
,is_backup_alert_enabled bit null
,time_since_last_copy int null
,last_copied_file nvarchar(500) null
,time_since_last_restore int null
,last_restored_file nvarchar(500) null
,last_restored_latency int null
,restore_threshold int null
,is_restore_alert_enabled bit null
,ts timestamp not null
,primary key (is_primary, server, database_name)
,unique (ts)
)
--
-- create other tables we will use
--
create table #secondary_monitor
(
secondary_server sysname not null,
secondary_database sysname not null,
secondary_id uniqueidentifier not null,
primary_server sysname not null,
primary_database sysname not null,
restore_threshold int not null,
threshold_alert int not null,
threshold_alert_enabled bit not null,
last_copied_file nvarchar(500) null,
last_copied_date datetime null,
last_copied_date_utc datetime null,
last_restored_file nvarchar(500) null,
last_restored_date datetime null,
last_restored_date_utc datetime null,
last_restored_latency int null,
history_retention_period int not null,
primary key (secondary_id, secondary_database)
)
create table #primary_monitor
(
primary_id uniqueidentifier primary key not null,
primary_server sysname not null,
primary_database sysname not null,
backup_threshold int not null,
threshold_alert int not null,
threshold_alert_enabled bit not null,
last_backup_file nvarchar(500) null,
last_backup_date datetime null,
last_backup_date_utc datetime null,
history_retention_period int not null,
unique (primary_server, primary_database)
)
--
-- get current time
--
select @curutcdate = getutcdate()
--
-- Enumerate the primary entries
--
declare #hcprimaries cursor local fast_forward for
select
primary_id
,primary_server
,primary_database
,backup_threshold
,threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
order by primary_server, primary_database
open #hcprimaries
fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
while (@@fetch_status != -1)
begin
--
-- we have a primary entry
--
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@primary_server
,@primary_database
,@backup_threshold
,@is_backup_alert_enabled)
--
-- process secondaries
--
if (upper(@primary_server) = upper(@@servername))
begin
--
-- we are on primary server
-- get monitor server information
--
select @monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_primary_databases
where primary_id = @primary_id
select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
--
-- enumerate the secondaries listed on primary
--
declare #hcprimarysecondaries cursor local fast_forward for
select secondary_server, secondary_database
from msdb.dbo.log_shipping_primary_secondaries with (nolock)
where primary_id = @primary_id
open #hcprimarysecondaries
fetch #hcprimarysecondaries into @secondary_server, @secondary_database
while (@@fetch_status != -1)
begin
--
-- add this primary secondary to result set
--
insert into #log_shipping_monitor (is_primary ,server, database_name)
values (0, @secondary_server, @secondary_database)
select @secondary_id = NULL
--
-- Enumerate this secondary from msdb.dbo.log_shipping_monitor_secondary
--
if (@is_monitor_local = 1)
begin
--
-- local monitor
--
select
@secondary_id = secondary_id
,@restore_threshold = restore_threshold
,@is_restore_alert_enabled = threshold_alert_enabled
,@last_copied_file = last_copied_file
,@last_copied_utc = last_copied_date_utc
,@last_restored_file = last_restored_file
,@last_restored_utc = last_restored_date_utc
,@last_restored_latency = last_restored_latency
from msdb.dbo.log_shipping_monitor_secondary
where primary_server = upper(@primary_server)
and primary_database = @primary_database
and secondary_server = upper(@secondary_server)
and secondary_database = @secondary_database
end -- local monitor
else
begin
--
-- remote monitor
--
if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
begin
--
-- execute as proxy
--
exec @retcode = sys.sp_MSproxylogshippingmonitorhelpsecondary
@monitor_server = @monitor_server
,@p1 = @primary_server
,@p2 = @primary_database
,@p3 = @secondary_server
,@p4 = @secondary_database
,@p5 = @secondary_id output
,@p6 = @restore_threshold output
,@p7 = @is_restore_alert_enabled output
,@p8 = @last_copied_file output
,@p9 = @last_copied_utc output
,@p10 = @last_restored_file output
,@p11 = @last_restored_utc output
,@p12 = @last_restored_latency output
end
else
begin
delete #secondary_monitor
select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_secondary '
,@retcode = 0
begin try
insert into #secondary_monitor
exec @retcode = @linkcmd
@secondary_server = @secondary_server
,@secondary_database = @secondary_database
end try
begin catch
select @retcode = 1
,@secondary_id = NULL
end catch
if (@retcode = 0)
begin
select @secondary_id = secondary_id
,@restore_threshold = restore_threshold
,@is_restore_alert_enabled = threshold_alert_enabled
,@last_copied_file = last_copied_file
,@last_copied_utc = last_copied_date_utc
,@last_restored_file = last_restored_file
,@last_restored_utc = last_restored_date_utc
,@last_restored_latency = last_restored_latency
from #secondary_monitor
where upper(primary_server) = upper(@primary_server)
and primary_database = @primary_database
end
else
begin
raiserror(32031, 10, 1, @secondary_server, @secondary_database, @monitor_server)
end
end
end -- remote monitor
--
-- do we have data on this secondary
--
if (@secondary_id is not null)
begin
--
-- yes we do - update the entry
--
select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
update #log_shipping_monitor
set
status = case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end
,time_since_last_copy = @time_since_last_copy
,last_copied_file = @last_copied_file
,time_since_last_restore = @time_since_last_restore
,last_restored_file = @last_restored_file
,last_restored_latency = @last_restored_latency
,restore_threshold = @restore_threshold
,is_restore_alert_enabled = @is_restore_alert_enabled
where upper(server) = upper(@secondary_server)
and database_name = @secondary_database
end -- update secondary data
--
-- fetch next primary secondary
--
fetch #hcprimarysecondaries into @secondary_server, @secondary_database
end
close #hcprimarysecondaries
deallocate #hcprimarysecondaries
end -- we are on primary server processing primaries
else
begin
--
-- we are on monitor server
-- get details of the secondaries from msdb.dbo.log_shipping_monitor_secondary
-- if the same monitor is being used by secondaries
--
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,time_since_last_copy
,last_copied_file
,time_since_last_restore
,last_restored_file
,last_restored_latency
,restore_threshold
,is_restore_alert_enabled)
select
case when (datediff(minute, last_restored_date_utc, @curutcdate) > restore_threshold
or last_restored_latency > restore_threshold) then 1 else 0 end
,0
,secondary_server
,secondary_database
,datediff(minute, last_copied_date_utc, @curutcdate)
,last_copied_file
,datediff(minute, last_restored_date_utc, @curutcdate)
,last_restored_file
,last_restored_latency
,restore_threshold
,threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_secondary (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database
end -- we are on monitor server processing primaries
fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
end -- while cursor for hcprimaries
close #hcprimaries
deallocate #hcprimaries
--
-- Enumerate the secondary entries
-- minus existing secondary entries in resultset
--
declare #hcsecondaries cursor local fast_forward for
select
secondary_server
,secondary_database
,secondary_id uniqueidentifier
,primary_server
,primary_database
,restore_threshold
,threshold_alert_enabled
,last_copied_file
,last_copied_date_utc
,last_restored_file
,last_restored_date_utc
,last_restored_latency
from msdb.dbo.log_shipping_monitor_secondary with (nolock)
where not exists (select * from #log_shipping_monitor
where upper(server) = upper(secondary_server)
and database_name = secondary_database
and is_primary = 0)
order by primary_server, primary_database
open #hcsecondaries
fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
@primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled,
@last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
while (@@fetch_status != -1)
begin
--
-- Have we processed the primary for this secondary
--
if not (upper(@primary_server) = upper(@prev_primary_server)
and @primary_database = @prev_primary_database)
begin
--
-- No - Try to get the details of this primary
--
select @primary_id = null
if (upper(@secondary_server) = upper(@@servername))
begin
--
-- we are on secondary
-- get monitor server information
--
select @monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_secondary with (nolock)
where secondary_id = @secondary_id
select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
if (@is_monitor_local = 1)
begin
--
-- local monitor
--
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database
end
else
begin
--
-- remote monitor
--
if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
begin
--
-- execute as proxy
--
exec @retcode = sys.sp_MSproxylogshippingmonitorhelpprimary
@monitor_server = @monitor_server
,@p1 = @primary_server
,@p2 = @primary_database
,@p3 = @primary_id output
,@p4 = @backup_threshold output
,@p5 = @is_backup_alert_enabled output
end
else
begin
delete #primary_monitor
select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_primary '
,@retcode = 0
begin try
insert into #primary_monitor
exec @retcode = @linkcmd
@primary_server = @primary_server
,@primary_database = @primary_database
end try
begin catch
select @retcode = 1
,@primary_id = NULL
end catch
if (@retcode = 0)
begin
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from #primary_monitor
end
else
begin
raiserror(32030, 10, 1, @primary_server, @primary_database, @monitor_server)
end
end -- processing remote
end
end -- processing on secondary
else
begin
--
-- we are on monitor server
--
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database
end -- processing on monitor server
--
-- insert primary details if available
--
if (@primary_id is not null)
begin
select @prev_primary_server = @primary_server
,@prev_primary_database = @primary_database
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@primary_server
,@primary_database
,@backup_threshold
,@is_backup_alert_enabled)
end -- primary data available
end -- process the primary
--
-- Insert the secondary
--
select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,time_since_last_copy
,last_copied_file
,time_since_last_restore
,last_restored_file
,last_restored_latency
,restore_threshold
,is_restore_alert_enabled)
values (
case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end
,0
,@secondary_server
,@secondary_database
,@time_since_last_copy
,@last_copied_file
,@time_since_last_restore
,@last_restored_file
,@last_restored_latency
,@restore_threshold
,@is_restore_alert_enabled)
--
-- get the next secondary
--
fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
@primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled,
@last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
end -- while cursor for hcsecondaries
close #hcsecondaries
deallocate #hcsecondaries
--
-- return resultset
--
SET @Body = RTRIM(@Body) + '<table cellspacing="0" cellpadding="0" width="100%">'
WHILE EXISTS (SELECT TOP 1 idx FROM #log_shipping_monitor)
BEGIN
SELECT TOP 1 @idx = idx, @status = (CASE WHEN status = 0 THEN 'Good' ELSE 'Bad' END),
@DB = database_name, @LastCopy = time_since_last_copy, @LastCFile = last_copied_file,
@LastRestore = time_since_last_restore, @LastRFile = last_restored_file,
@Latency = last_restored_latency, @Threshold = restore_threshold
FROM #log_shipping_monitor order by ts
SET @Body = RTRIM(@Body)
+ '<tr><td nowrap style="font-size:11px;font-family:Verdana">'
+ '<b>Database</b>:' + RTRIM(@DB) + '
'
+ CASE WHEN @status = 'Good' THEN
'<b>Status</b>:' + RTRIM(@Status) + '
'
ELSE '<b>Status</b>:<font size="+1" color="red">' + RTRIM(@Status) + '</font>
' END
+ '<b>Last Copied File</b>:' + RTRIM(@LastCFile) + '
'
+ '<b>Time since last Copy (min)</b>:' + RTRIM(@LastCopy) + '
'
+ '<b>Last Restored File</b>:' + RTRIM(@LastRFile) + '
'
+ '<b>Time since last Restore (min)</b>:' + RTRIM(@LastRestore) + '
'
+ '<b>Restore Latency (min)</b>:' + RTRIM(@Latency) + '
'
+ '<b>Restore Threshold (min)</b>:' + RTRIM(@Threshold) + '
'
+ '</td></tr>'
+ '<hr style="width: 100%; height: 1px;"> '
DELETE FROM #log_shipping_monitor WHERE idx = @idx
END
SET @Body = RTRIM(@Body) + '</table>'
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @Recipients
,@subject = @Subject
,@body = @Body
,@body_format = 'HTML'
end
DROP Table #log_shipping_monitor
DROP TABLE #primary_monitor
DROP TABLE #secondary_monitor
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 1, 2012 at 10:17 pm
Thank You So much..yes i can understand that code....
but that , i can run the scheduled sql job right.automatically,every Time Run scheduled Job so every TRN should be directly came To Mail ..right.
so for example my TRN file every one hour take the back up send to via email.so per day 24 trn files.so i want last backup file TRn and last restoration TRN file in the day for logshipping databases? is it possible Or not???? and Timestampe also...
Please Help me.
SQL server DBA
March 2, 2012 at 7:58 am
kranthi.india123 (3/1/2012)
Thank You So much..yes i can understand that code....but that , i can run the scheduled sql job right.automatically,every Time Run scheduled Job so every TRN should be directly came To Mail ..right.
so for example my TRN file every one hour take the back up send to via email.so per day 24 trn files.so i want last backup file TRn and last restoration TRN file in the day for logshipping databases? is it possible Or not???? and Timestampe also...
Please Help me.
You need to do some of this work yourself...
If you schedule the script as a SQL Agent job and set it to run every hour, yes, you will receive 24 mails/day.
If you just want the last one at the end of the day, create a SQL Agent job and schedule it to run once a day, at 11:59:57pm
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 31, 2012 at 3:24 am
Dear MyDoggieJessie,
Thanks for this script.
But when i execute thsi script a get the message:
Conversion failed when converting the nvarchar value '<Primary_Server>\Instance data type bit.
I'am using SQL 2005
Can you help me to run this script.
Peter Verstappen
May 31, 2012 at 11:00 am
Please post the exact code you are using and I will take a look at it - thanks
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 1, 2012 at 1:50 am
Dear MyDoggieJessie
First i made a mistake in de recipient parameter this is solved
Then i drop the 3 temp databases.
When i execute the script i got the message
Msg 245, level 16, Sate 1, Line 127
Conversion failed when converting the nvarchar value '<Primary_Server>\<Instancename>' data type bit.
When i execute the script again i get teh message
Msg 2714, level 16, Sate 6, Line xx
There is already a object name '#log_shipping_monitor' in the database.
In tempdb
i see the 3 table, but these are empty.
Should i run this as a job in the Job Aagent?
Peter Verstappen
The Netherlands
June 1, 2012 at 11:35 am
Make sure the temp tables are dropped. when you first executed the script the temp tables were created then when you attempted to rerun it, it fails because that table already exists.
It would be cleaner to add an IF EXISTS in the beginning of the procedure/script, this would then drop the table should they already exists...
Again, please post your script(s) when you are needing assistance (it's easier for someone to help)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 23, 2012 at 2:56 pm
I had it failed with similar error :
Msg 245, level 16, Sate 1, Line 127
Conversion failed when converting the nvarchar value '<Primary_Server>\<Instancename>' data type bit.
The issue was with inserting a varchar value to pre-defined variable @is_primary which is of datatype "bit "
In the code it does an insert:
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@primary_server ----This is defined as bit in #log_shipping_monitor whereas the value passed here is varchar
,@primary_database
,@backup_threshold
,@is_backup_alert_enabled)
I see this script working very well in secondary server but on primary server, even after I fixed the datatype to sysname, it sends an empty email with no HTML.
July 25, 2012 at 1:59 pm
Of course it doesn't work on the Primary Server...It's not supposed to. The OP asked for an automated notification of all log shipped DB's. Log shipping is in essence an actions from the Primary to the secondary...not the other way around.
When the CURSOR originally gets created declare #hcprimaries cursor local fast_forward for
select
primary_id
,primary_server
,primary_database
,backup_threshold
,threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
order by primary_server, primary_database
Nothing gets returned (because the values don't even exist on the secondary server, because the initial table column is a "is_primary bit not null default 0", this value gets set to 0 (and not the actual server name).
This is of course, by design.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 84 total)
You must be logged in to reply to this topic. Login to reply