监控logshipping 流量

 --------因环境不同,请勿直接copy使用

 

1 --create procedure 
create procedure usp_logshipping_send 
as 
declare  
 @hostname sysname 
,@px int 
,@sql varchar(500); 
if OBJECT_ID(N'hostinfologshipping','U') is not null 
drop table hostinfologshipping; 
create table hostinfologshipping 
(id  int identity(1,1), 
 hostname sysname, 
 primary_database sysname, 
 backup_directory sysname 
); 
with hostname 
as 
( 
select distinct HostName 
 from db_allhostinfo_database where IsLogShipping=1  and HostName<>'192.168.1.122'
 ) 
 select *,px=ROW_NUMBER()over(order by hostname) 
 into #hostname 
  from hostname; 
  select @hostname=quotename(hostname),@px=px from #hostname  
  where px=1; 
 while @@ROWCOUNT>0 
 begin 
  set @sql='insert into hostinfologshipping(hostname,primary_database,backup_directory) select '''+replace(replace(@hostname,'[',''),']','')+'''as hostname,* from openquery('+@hostname+',''select primary_database,backup_directory from msdb.dbo.log_shipping_primary_databases '') as b' 
  execute(@sql); 
  select top(1) @hostname=quotename(hostname),@px=px from #hostname where px>@px;  
 end

 

--2 -ssis package

 

3 every 10 minute get logshipping info for ssis package job

4 ssrs