Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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).

share|improve this answer
add comment

Try increasing the -QueryTimeOut of the agent.

share|improve this answer
    
it would need to take more than 30 min? –  Daniel Schaffer May 21 '09 at 15:54
    
Depends on your data size. –  Sam May 21 '09 at 19:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.