Latency in Replication Monitor

If my publications are not actively receving any changes on the Publishing
end, will the latency increase ? If there are no transactions/commands
being pushed to the subscriber , then how is latency calculated ? I have
setup replication and its been idle for a few days now and the latency shows
7 secs.. Thanks
latency reflects the last transaction/commands replicated. So 7 secs was the
latency of the last delivered transaction/command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23tPZ7s38EHA.3012@.TK2MSFTNGP09.phx.gbl...
> If my publications are not actively receving any changes on the Publishing
> end, will the latency increase ? If there are no transactions/commands
> being pushed to the subscriber , then how is latency calculated ? I have
> setup replication and its been idle for a few days now and the latency
shows
> 7 secs.. Thanks
>
|||I have stopped the distribution agent for a day now and the latency still
shows 7 secs.. Shouldnt it have increased ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uUziGP68EHA.2900@.TK2MSFTNGP09.phx.gbl...
> latency reflects the last transaction/commands replicated. So 7 secs was
the[vbcol=seagreen]
> latency of the last delivered transaction/command.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23tPZ7s38EHA.3012@.TK2MSFTNGP09.phx.gbl...
Publishing
> shows
>
|||No, it will only reflect the new latency when you restart it. Then IIRC, it
will reflect a latency of >>7 days.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eSsxsQD9EHA.1392@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have stopped the distribution agent for a day now and the latency still
> shows 7 secs.. Shouldnt it have increased ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uUziGP68EHA.2900@.TK2MSFTNGP09.phx.gbl...
> the
> Publishing
transactions/commands[vbcol=seagreen]
have
>
|||Hmm.. now what i wanted.. How do you guys prefer to have some replication
monitor alerts.. Im setting up transactional replication and would like to
be alerted if my subscribers are more than 5-10 mins behind my publisher.
Also to note that if the publisher doesnt have any changes to replicate ,
then the alert should not fire.. Please advise
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23WwWYeD9EHA.2196@.TK2MSFTNGP14.phx.gbl...
> No, it will only reflect the new latency when you restart it. Then IIRC,
it[vbcol=seagreen]
> will reflect a latency of >>7 days.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eSsxsQD9EHA.1392@.tk2msftngp13.phx.gbl...
still[vbcol=seagreen]
was[vbcol=seagreen]
> transactions/commands
> have
latency
>
|||I monitor my distribution history tables. If the values in the time column
get beyond a certain point I raise an alert which write the event log. NetIQ
then scrapes the event log and sends out pages.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OV6x6uE9EHA.2804@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Hmm.. now what i wanted.. How do you guys prefer to have some replication
> monitor alerts.. Im setting up transactional replication and would like to
> be alerted if my subscribers are more than 5-10 mins behind my publisher.
> Also to note that if the publisher doesnt have any changes to replicate ,
> then the alert should not fire.. Please advise
> Thanks
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23WwWYeD9EHA.2196@.TK2MSFTNGP14.phx.gbl...
> it
> still
> was
I
> latency
>
|||Could you send me the sample query that I could use ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eAxUOGQ9EHA.2600@.TK2MSFTNGP09.phx.gbl...
> I monitor my distribution history tables. If the values in the time column
> get beyond a certain point I raise an alert which write the event log.
NetIQ[vbcol=seagreen]
> then scrapes the event log and sends out pages.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OV6x6uE9EHA.2804@.TK2MSFTNGP15.phx.gbl...
replication[vbcol=seagreen]
to[vbcol=seagreen]
publisher.[vbcol=seagreen]
,[vbcol=seagreen]
IIRC,[vbcol=seagreen]
secs[vbcol=seagreen]
?
> I
>
|||Try something like this
DECLARE @.count int
SELECT @.count = COUNT(max_time) FROM
MSdistribution_agents, (SELECT agent_id, max_time=MAX(time) FROM
MSdistribution_history GROUP BY agent_ID ) AS a
WHERE a.agent_id = MSdistribution_agents.id
AND max_time < DATEADD(d,-1,getdate())
IF @.count >0
BEGIN
DECLARE @.SubscriberName sysname
DECLARE expiringSubscriber_cursor CURSOR FOR
SELECT SubscriberName = (SELECT srvname FROM master.dbo.sysservers
WHERE srvid = subscriber_id) FROM
MSdistribution_agents, (SELECT agent_id, max_time = MAX(time) FROM
MSdistribution_history GROUP BY agent_ID ) AS a
WHERE a.agent_id = MSdistribution_agents.id
AND max_time < dateadd(d,-1,getdate())
ORDER BY 1
OPEN expiringSubscriber_cursor
FETCH NEXT FROM expiringSubscriber_cursor
INTO @.SubscriberName
WHILE @.@.FETCH_STATUS = 0
BEGIN
RAISERROR (50001, 19, -1, @.SubscriberName) with log
FETCH NEXT FROM expiringSubscriber_cursor
INTO @.SubscriberName
END
CLOSE expiringSubscriber_cursor
DEALLOCATE expiringSubscriber_cursor
END
Code Sample 10.2 is an example of the alert Code Sample 10.1 uses.
Code Sample 10.2
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = 'Expiring
Subscriber Alert'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = 'Expiring Subscriber Alert'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = 'Expiring Subscriber Alert',
@.message_id = 50001, @.severity = 0, @.enabled = 1, @.delay_between_responses =
60, @.include_event_description_in = 5, @.event_description_keyword =
'Subscriber %s is expiring', @.category_name = '[Uncategorized]'
END
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1ge68v9EHA.208@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Could you send me the sample query that I could use ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eAxUOGQ9EHA.2600@.TK2MSFTNGP09.phx.gbl...
column[vbcol=seagreen]
> NetIQ
> replication
like[vbcol=seagreen]
> to
> publisher.
replicate[vbcol=seagreen]
> ,
> IIRC,
latency[vbcol=seagreen]
> secs
the[vbcol=seagreen]
calculated
> ?
>

Large objects and the transaction log

Hi there!

I'm a little bit in doubt because of the behavior of the transaction log in the case i have large transactions inserting and deleting a lot of (i.e. 100) large objects (data type image) with a size (each) about 20MB. I expected that the transaction log should grow with 2GB each of this large transactions. But it does'nt.

Are these large transactions not logged - so that in case of disaster all lobs since last diff- or full-backup are lost?

Some experience with that?

Greetings, Torsten

First off, I assume that your database in not in Simple recovery mode, right? If it is, then the transaction log does not accumulate data from transactions that are completed.

Can you describe the transactions a bit more? When you say that LOBs are inserted and deleted in the large transaction, are the same LOBs created and deleted within one transaction, or are you inserting some and deleting others?

Remember also that unless you explicitly shrink the log, it will reach a steady state where it has the capacity used by all transactions between log backups. It should not just keep growing.

What is the size of your log, and how many of these transactions occur between backups?

|||The problem is no problem. I've not recognized that you have to set an explicit file space for lob columns. That has been the default filespace (Primary), so there is no surprise that only the primary filegroup growth...

Large objects and the transaction log

Hi there!

I'm a little bit in doubt because of the behavior of the transaction log in the case i have large transactions inserting and deleting a lot of (i.e. 100) large objects (data type image) with a size (each) about 20MB. I expected that the transaction log should grow with 2GB each of this large transactions. But it does'nt.

Are these large transactions not logged - so that in case of disaster all lobs since last diff- or full-backup are lost?

Some experience with that?

Greetings, Torsten

First off, I assume that your database in not in Simple recovery mode, right? If it is, then the transaction log does not accumulate data from transactions that are completed.

Can you describe the transactions a bit more? When you say that LOBs are inserted and deleted in the large transaction, are the same LOBs created and deleted within one transaction, or are you inserting some and deleting others?

Remember also that unless you explicitly shrink the log, it will reach a steady state where it has the capacity used by all transactions between log backups. It should not just keep growing.

What is the size of your log, and how many of these transactions occur between backups?

|||The problem is no problem. I've not recognized that you have to set an explicit file space for lob columns. That has been the default filespace (Primary), so there is no surprise that only the primary filegroup growth...

Large insert, how to switch off transactions?

Hello, I've a database with 8 million rows on my development pc and I've got
about 8 GB free disk space.
I have 3000 duplicate records within the table.
I ran a 'distinct' query and put them into a temp table and I am pulling
them back in again but after 2 hours I have ran out of transaction space. I
still have my records in the p# temp table but can't do an sp_rename on it
(probably because it is temporary).
Any way to run an insert like this but not write a transaction log out?
Thanks, Kevin Munro.
My sql is :
select distinct propertyid,pointid,containerid,value
into #p from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #pDo not do it using SELECT ... INTO ..., it consumes a lot of resources. Try:
select propertyid, pointid, containerid, value
into #p from propertieslink where 0 = 1
insert into #p
select distinct propertyid, pointid, containerid, value
from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
-- or
select propertyid,pointid,containerid,value
into #p from propertieslink where 0 = 1
insert into #p
select propertyid, pointid, containerid, value
from propertieslink
group by
propertyid, pointid, containerid, value
having
count(*) > 1
delete
a
from
propertieslink as a
inner join
#p as b
on a.propertyid = b.propertyid
and a.pointid = b.pointid
and a.containerid = b.containerid
and a.value = b.value
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
AMB
"Kevin Munro" wrote:

> Hello, I've a database with 8 million rows on my development pc and I've g
ot
> about 8 GB free disk space.
> I have 3000 duplicate records within the table.
> I ran a 'distinct' query and put them into a temp table and I am pulling
> them back in again but after 2 hours I have ran out of transaction space.
I
> still have my records in the p# temp table but can't do an sp_rename on it
> (probably because it is temporary).
> Any way to run an insert like this but not write a transaction log out?
> Thanks, Kevin Munro.
> My sql is :
> select distinct propertyid,pointid,containerid,value
> into #p from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
>
>|||If you are just trying to get rid of the 3000 duplicates, there are easier
ways to do it.
A clever query with HAVING COUNT(*) would do the trick.
Regards
Mike
"Alejandro Mesa" wrote:
> Do not do it using SELECT ... INTO ..., it consumes a lot of resources. Tr
y:
> select propertyid, pointid, containerid, value
> into #p from propertieslink where 0 = 1
> insert into #p
> select distinct propertyid, pointid, containerid, value
> from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> -- or
> select propertyid,pointid,containerid,value
> into #p from propertieslink where 0 = 1
> insert into #p
> select propertyid, pointid, containerid, value
> from propertieslink
> group by
> propertyid, pointid, containerid, value
> having
> count(*) > 1
> delete
> a
> from
> propertieslink as a
> inner join
> #p as b
> on a.propertyid = b.propertyid
> and a.pointid = b.pointid
> and a.containerid = b.containerid
> and a.value = b.value
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> AMB
>
> "Kevin Munro" wrote:
>|||It was included as a possible solution (second one). Your comment is welcome
anyway.
Thanks,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> If you are just trying to get rid of the 3000 duplicates, there are easier
> ways to do it.
> A clever query with HAVING COUNT(*) would do the trick.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>|||Thanks for these suggestions, the one I thought of would have been ok in a
smaller table and I'll do the HAVING COUNT query.
I got around it by dropping the indexes and it only took 5 minutes. I am
now applying the indexes one at at time and this is somewhat
transactionalising it I think.
Kevin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:590BB8B7-640D-4530-948A-D868A5B42477@.microsoft.com...
> It was included as a possible solution (second one). Your comment is
> welcome
> anyway.
> Thanks,
> Alejandro Mesa
>
> "Mike Epprecht (SQL MVP)" wrote:
>