SQL Replication - Snapshot agent

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"?
Anonymous KHIT EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pushpakumara MahagamageVPCommented:

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anonymous KHIT EngineerAuthor Commented:

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 EngineerCommented:
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Pushpakumara MahagamageVPCommented:
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 EngineerAuthor Commented:

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 EngineerAuthor 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?
Pushpakumara MahagamageVPCommented:
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 EngineerAuthor 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
Pushpakumara MahagamageVPCommented:
1.Have you already stop the snapshot agent
2. is it 100% completed.
anyway just check this blog that might help you.  
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.