sql server replication: Distribution agent locks during post snapshot script for queued updating transactional subscribers -- sql server replication

Distribution agent locks during post snapshot script for queued updating transactional subscribers

pnetherwood NO[at]SPAM email.com
8/14/2003 3:57:06 AM

sql server replication:

I am using transactional replication with some subscribers as queued
updateable subscribers. I have a post snapshot script which queries
the permission table on the publisher and applies the permissions to
the local objects on the subscriber. (This is to get round the probelm
of role permissions not replicating. Querying the permission table on
the publisher allows us to be sure the permissions are the same on the
subscribers.)

However the post snapshot script never finishes. The distribution
agent on the publisher eventually timeouts. On the subscriber you can
see the osql process waiting. It never finishes and requires a reboot
of both the publisher and subscriber to clear the process. It is
locked on MSsubscription_articles and MSreplication_subscriptions in
the subscriber.

My post snapshot script starts with:

DECLARE aPermissionCursor CURSOR fast_forward FOR
select so.ID, so.NAME, so.TYPE from BCMSRV11.ProdTS.dbo.sysobjects so
where so.replinfo in (514, 35)
ORDER BY so.type, so.name

[The so.replinfo in (514, 35) part just selects the elements in
sysobjects that I have published]

[BCMSRV11.ProdTS is the publisher and is accessed from the subscriber
via a linked server]

When I look at the process properties it says that this was the last
statement that was run.

The script works fine if I apply it manually myself using osql after
the snapshot from the publisher. The script also works fine on
non-updatable subscriptions when applied as a post snapshot script.

I'm running out of ideas on how to bug fix this. Particularly since
the distribution agent does not display any helpful messages. Any
ideas?

I'm running:
Windows 2000
SQL Server 2000 SP2
on the publisher and all subscribers.

Regards

Paul Netherwood

Re: Distribution agent locks during post snapshot script for queued updating transactional subscribers

Greg Yvkoff [MSFT]
8/14/2003 8:39:45 AM

at the time it hangs, you should run sp_who, sp_who2, sp_lock, etc. to see
what is blocking. That will shed some more light as to what may be the
reason for the hang.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

thanks.
greg

[quoted text, click to view]