2

Before I begin - I'm not a DBA type, I'm a software dev type. Our DBA has been out sick for the last week or so, and I've been trying to get a new feature of ours up and running.

This new feature involved adding a couple tables and views to an existing replication subscription, so I added the articles to the subscription using the UI in SSMS. Nothing happened, so I tried running the snapshot agent.

Looking at the monitor for the snapshot agent, it appears that everything actually finishes within the first 3-4 seconds of it running. In the log I see things like:

[0%] Activated articles for publication '[publicationname]' at the publisher.

[0%] Bulk copying snapshot data for article 'new_table_name'

[0%] Bulk copied snapshot data for article 'new_table_name' (1068 rows).

(previous two rows repeat for each new table)

Then the progress skips from 0% to 93%:

[93%] Pre-loading meta-data of all tables in the publisher database for scripting

[93%] Pre-loading meta-data of all stored procedures in the publisher database for scripting

[93%] Pre-loading meta-data of all views in the publisher database for scripting

[93%] Pre-loading meta-data of all user-defined functions in the publisher database for scripting

[93%] Sorting article objects in the proper creation order

Then, I start seeing lots of these:

[93%] The published object [objectnamehere] (StoredProcedure) is dependent on the unpublished object [objectnamehere] (Table), creation of the published object at the subscriber may fail unless the dependency is satisfied ahead of time.

But they're all for things I don't particularly care about... but I'm not positive I can remove them from the publication without breaking anything. There are ~20-30 of these.

Then, the agent runs for a bit without doing anything. After 10 min I get this:

[93%] The process is running and is waiting for a response from the server.

And then two more every 10 mins until it times out:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Now, it looks like the regular transaction replication isn't working either because there's no good current snapshot.

Needless to say, I'm in it pretty deep and I REALLY need some help. Thanks.

2 Answers 2

3

Turns out I had a couple other jobs running that were blocking the snapshot agent. As explained here: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-replication/8439/Timeout-error-running-snapshot-agent

The snapshot agent requires an exclusive lock, so it was timing out waiting for the lock. Disabling the jobs and then re-running the agent worked (and it only took ~5 sec).

1

Try increasing the -QueryTimeOut of the agent.

2
  • it would need to take more than 30 min? May 21, 2009 at 15:54
  • Depends on your data size.
    – Sam
    May 21, 2009 at 19:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .