SQL Server Performance Forum – Threads Archive

HELP: REPLICATION CRASH

I desperately need help and amd new to SQL SERVER. I had the following disaster happen to me. 1. There are two database servers with replication turned on.
2. SQL-B has a database called DB is pushing data to SQL-A.
3. On SQL-A, there are SQL Agent Jobs that are running REPLICATION DISTRIBUTOR
4. SQL-B crashes severly to the point where DB is lost.
5. A successful restore of DB was accomplished by getting an earlier backup.
6. A few days passed and it was noted that the jobs mentioned in step #3 have dissappeared. It is not in MSDB and sad to say, MSDB was nevered backup.
Questions:
a. Could the a crash on one server cause the disapperance of jobs on the a second server? Since there were 4 jobs that belong to the same replicated database, its hard to rule this out like someone accidently deleted them. b. Is looks like to me that the jobs mentioned in step #3 were created by SQL Server through some kind of wizard. Is this true?
PLEASE RESPOND ASAP!!!

It most probably that SQL Server deleted you subsription and together with that
the job that distribute data. There is job on server called
"Expired subscription clean up" If your server shutdown and replication failed for certain amount of time
then your subscription will be expired. I disabled that job on my server

You have to recreate the sucscription

What is the specific "sp" command that will do that? From what I can see of the jobs that fit your description, they are, sp_replication_agent_checkup
sp_MSreinit_failed_subscriptions
sp_MSdistribution_cleanup
sp_MShistory_cleanup Thanks

First at all it’s a job I refered above. To recreate subscription: 1.On you publisher server go to Tools->Replication-&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ush Subscription to Others on your tool bar. 2.Highlight your publication. 3.Click Push new Subscription 4. Follow the wizard

Questions:
a. Could the a crash on one server cause the disapperance of jobs on the a second server? Since there were 4 jobs that belong to the same replicated database, its hard to rule this out like someone accidently deleted them.

Yes if the other server service is crashed completed then ensure MSDB is working, if not you need to restore to get those jobs back to schedule.

b. Is looks like to me that the jobs mentioned in step #3 were created by SQL Server through some kind of wizard. Is this true?

If you haven’t used any scripts to create replication then yes, EM will create when you use Replication wizard. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>