SQL Replication - Snapshot agent

Anonymous KH
Anonymous KH used Ask the Experts™
on
Dear Experts,

MS SQL 2014 Enterprise

I am assisting my client to run the SQL replication.

However, there was a database that is configured to use DB restore with norecovery and restore with a differential backp.

I later accidentally ran the snapshot agent and now it keeps displaying "the replication agent has not logged a progress message in 10 minutes".

How can I set it back to "Never started"?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

Actually i didn't understand your question properly. Anyway Replication agents checkup job is schedule for 10 min interval by default. check the job history.

you can clear job history  https://msdn.microsoft.com/en-us/library/ms179338%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396

or use, sp_purge_jobhistory


is there any impact to replication, by they way which replication you are using to replicate. you can use transaction replication instead of restore backups.  and you can use log shipping also.
Anonymous KHIT Engineer

Author

Commented:
Hi!

The snapshot agent is not suppose to run.

This is what we do for the SQL replication for very big database size.

1. A full DB backup from the Prod DB was done a few days back.
2. On the same server we will create a publication of the DB and set the properties to allow initialization of backup files.
3. We will the create a differential backup of the same DB.
4. Then on the destination server, we will do a full restore of the DB but with norecovery
5. We will also restore back the differential backup of the DB
6. From the Prod DB, we will create a subscription script for the destination server
7. After the script is executed, the SQL replication starts.

The above process is done but without running the snapshot agent due to its large database size.

The issue is I accidentally click the snapshot agent to start when there is no need  for it to run at all.

In the other DBs, the snapshot agent's status is "never started".

So I would like to reverse the activation of the snapshot agent so that there is no error message about it.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017
Commented:
A replication should always start with a Snapshot. You might no need to perform a snapshot again but at the first time is really needed, otherwise the Replication won't work.

I couldn't understand if you're replicating from the PROD or from the restored DB. Can you elucidate us, please?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

there is transaction initialization without snapshot - https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/ 

Kevin followed that method i guess, this method is good if you don't have to change article and filters after start the replication.  

But this method you have to disable snapshot agent.  

Kevin check whether your replication is working, if so disable snapshot agent. anyway read the given article again to check whether you have missed any step.
Anonymous KHIT Engineer

Author

Commented:
Hi!

The snapshot agent is not needed but accidentally started and so it is giving unnecessary errors.

I am replicating from the Prod server to another server that is to manage all the DB replication so that users only access one server instead of multiple servers to get the report.

The Prod servers are only replicating 2 Articles.
Anonymous KHIT Engineer

Author

Commented:
Hi! Pushpakumara Mahagamage,

I am following exactly the instructions from your link.

But I accidentally ran the snapshot agent, so it there anyway to reverse it nack or do I need to do the whole replication with the sql database restore again?
Let me know your .MDF size, and your requrement, I mean is that secondary DB for OLAP DB or DR db etc.  then I can suggest you the best method.  

disable the snapshot agent schedule job. [there is a job schedule to every 10min and then check again whether data is replicating to secondary DB. if so you can keep your replication.

otherwise you have to start again from the binning.

I think the best option is create replication from the beginning.

Actually I have replication with snapshot agent because I have to apply DB changes and replication changes time to time according to the requirement. then I can reinitialize the snapshot, adjust replication articles and filters. Then changes replicate to the subscriber. My databases are not so big they are about 300GB so  initialize a snapshot is not a big job. I have test initialize replication from DB backup method also. but never start snapshot agent.

I'll do some research on it and get back to you.
Anonymous KHIT Engineer

Author

Commented:
The .MDF size varies from 200GB to 13TB.

The secondary DB is to consolidate all the DBs together so that users do not have to login to multiple servers / clusters to do view their reports.

They are just login into one server and they can see all the DBs at once.

My aim is to disable the snapshot agent as it keeps reporting the 10min error message.

If I can set it back to never run then there will be no errors or white crosses with red background
1.Have you already stop the snapshot agent
2. is it 100% completed.
anyway just check this blog that might help you.  
http://blogs.lobsterpot.com.au/2012/12/26/sql-replication-demystified/
I couldn't recreate your situation, I mean that alert not generating after ruining the snapshot agent. but i'm doing my experiment on that.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial