merge replication - snapshot-agent - timeout - sql server 2008

Question

Hi,

I have a SQL Server 2008 database, and I need a mergereplication because i want to sync with mobile devices afterwards.

So I created a replication but when it comes to start the snapshotagent, the agent tries to start for about 20 minutes and then it shows the message

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high systemactivity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

There aren't any other errormessages, neither in the snapshot-agent-status-window nor in the agent-log-window.

I don't have the administrator of the domain, but the local administrator and a domainuser with admin-privilegs. Both have all rights to database, are in the access-list of the replication.

The server agent runs on the local administrator-account and there are 3 merge replications on the server, working

The job runs also under the local administrator.

Thank you for your help, kwuadrat

EDIT:    

I found a entry in the Log:

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'DOMAIN\Administrator', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

Does this problem has something to do with my initial problem?

Answer 1

What is the account on which your Merge Agent is running? You can run below query and see if it runs fine:

xp_logininfo 'DOMAIN\Administrator'

---

Thanks, Santosh. Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

The agent is running under the local administrator account. I have also tried my domain user with no positiv results...

When I tried your query xp_logininfo 'DOMAIN\Administrator' I get the same error:

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user
'DOMAIN\Administrator', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

The same error occures, if i try the query with my user:

xp_logininfo 'DOMAIN\User'

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user
'DOMAIN\User', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

I'm logged on as User an have full rights...

If i try the query with my local admin (SERVER\Administrator) i receive this:

account name    type    privilege    mapped login name    permission path
SERVER\Administrator    user    admin    SERVER\Administrator    NULL

Answer 3

Chagne the job properties to have the job owner as sa - even if you use windows authentication only.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

kwuadrat - The errors might be unrelated.  You can enable verbose agent logging to file to verify and see where the Snapshot Agent is getting stuck.  Add the parameters -OutputVerboseLevel 2 -Output C:\TEMP\snapshotagent.log to the run agent job step, re-run the Snapshot Agent job, and inspect the log.

http://support.microsoft.com/kb/312292

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high systemactivity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Sometimes this agent message is transitory and goes away after the agent finishes processing.  How big of a snapshot are you generating?

---

Brandon Williams (blog | linkedin)

Answer 5

So it works again...

Maybe someone else has got the same issue one day, so i post the solution here:

I researched on the server and found out, the sql server service is running under a local user. The reason for this is, that there were problems with the backupsystem, used by our customers and so they changed it years ago.

Because of the local user account the 15404-Error occures.

Knowing, that i mustn't use domain-accounts, I also solved the initial problem with my snapshot-agent. I searched for hours (nearly days ;) ) and it was just this little change:

When the Replication is created, the job is created too. The job has three steps. As written above the Job-owner is the local-admin, also for the server-agent-service. But the second step of my job (replictionsnapshot) has one setting: run as. And by default this isn't the job-owner but the user running the creation, in my case my domain-account.

Now, that I set it to the local-administrator as well everything works fine again.

Thanks for your help, kwuadrat

Answer 6

I am having this same issue when trying to replicate a 40g database.

I followed the Books Online Lessons to set this up and after the Creating Snapshot step failed a few times, I tried it with just one table selected as an Article and it worked fine.

I have full Admin access on everything and am not sure if the issue is being caused by SQL Server Agent running as a domain user and am leary of going in and changing these options in our setup.

Is there way to increase the QueryTimeout  value - I couldn't find where to do this and saw it on a previous post from 2009.

Thanks in Advance

Answer 7

A good way to speed this up is to precreate your guid keys.

Ie - do this to every table you are replicating.

alter table test add  rowguid uniqueidentifier  ROWGUIDCOL default newsequentialID()
GO
alter table test add  constraint rowguid_test unique (rowguid)
GO

where test is the name of the table.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 8

Thanks Hilary, but we have over 564 tables.

I just tried another reduced set of tables (about 120) - none are very large and it still timed out after 19 mins...very frustrating and ONCE again MSFT seems to have a nice feature but fails on delivery and documentation on how to implement correctly in real world scenarios.

So now I look for alternative, tired of playing around with it.

Your book looks interesting and saw a chapter on Replication - might be worth a look...thanks again.

Answer 9

The replication chapter is not in great detail. It likely will not work for you.  Can you see if there is blocking going on from user processes? I have set query timeout to hours on occasion - for very large tables.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941