Publishers disappeared from under replication monitor

We have this problem on one of our servers. We tried restarting the agent
service but that did not resolve this. Is is so crucial that we identify a
maintenance window asap to take care of this or do you think that it can
wait a few days? It does not seem to be causing any problems. The machine
will most likely be rebooted in the near future for security patches,
anyway.
Michelle
You hit the nail on the head. Restarting the SQL agent cured the problem.
Many thanks!
Ray
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O96$4HLVEHA.2992@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> that's your problem. Bounce sql server agent. If this does not solve it,
> bounce sql server.
> "Ray Price" <ray.price@.gartner.com> wrote in message
> news:%23dff%237HVEHA.3944@.tk2msftngp13.phx.gbl...
> have
> Enterprise
though.
>
what happens when you issue this command?
sp_MSload_replication_status
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"michelle" <michelle@.nospam.com> wrote in message
news:%23UIngtjfEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> We have this problem on one of our servers. We tried restarting the agent
> service but that did not resolve this. Is is so crucial that we identify a
> maintenance window asap to take care of this or do you think that it can
> wait a few days? It does not seem to be causing any problems. The machine
> will most likely be rebooted in the near future for security patches,
> anyway.
> Michelle
>
> You hit the nail on the head. Restarting the SQL agent cured the problem.
> Many thanks!
> Ray
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:O96$4HLVEHA.2992@.TK2MSFTNGP12.phx.gbl...
publications
> though.
>
>
|||Same as Ray:
Server: Msg 208, Level 16, State 1, Procedure sp_MSload_replication_status,
Line 14
Invalid object name 'tempdb.dbo.MSreplication_agent_status'.
Which is why I restarted the agent service. It didin't help me out though.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ewTOZxjfEHA.2468@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> what happens when you issue this command?
> sp_MSload_replication_status
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:%23UIngtjfEHA.632@.TK2MSFTNGP12.phx.gbl...
agent[vbcol=seagreen]
a[vbcol=seagreen]
machine[vbcol=seagreen]
problem.[vbcol=seagreen]
it,[vbcol=seagreen]
helps?
> publications
>
|||Can you give me a history of this problem.
Note that if you script out publications and recreate them on the same
server but in different databases and have the same snapshot agent and
distribution agent name you can get a condition like this.
By chance did you do something like this?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"michelle" <michelle@.nospam.com> wrote in message
news:u$PVj7jfEHA.720@.TK2MSFTNGP11.phx.gbl...
> Same as Ray:
> Server: Msg 208, Level 16, State 1, Procedure
sp_MSload_replication_status,[vbcol=seagreen]
> Line 14
> Invalid object name 'tempdb.dbo.MSreplication_agent_status'.
> Which is why I restarted the agent service. It didin't help me out though.
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ewTOZxjfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> agent
identify[vbcol=seagreen]
> a
can
> machine
> problem.
> it,
> helps?
>
|||Not exactly but what was done could have had the same effect...
We had a problem a couple of weeks ago. I'll try to make a long story short.
Basically the only database on the server being replicated suddenly had 'no'
tables showing up. This happened at the same time that the job that backup
the transaction logs failed (SQL LiteSpeed). We have a support case open
with MS but at this point they are asking that we pursue it with LiteSpeed
(yet we do not have a support contract). We were getting assertion errors.
Not sure if you care to hear about the details of that...
This seemed like some kind of corruption in the database. We thought that we
would first try to detach and re-attach the files but since it was being
replicated, we were unable to do so because it was being replicated. One of
the other DBAs here 'deleted everything from the distribution database' (I
think that's what he said). I explained that the user database kept
replication 'stuff' in it, too so that would not do any good. Too Late.
Even a restore would require that we get rid of the existing database so we
restarted the sql server service to see what happened. Lo-and-behold, the
database came back up.
The next day we set up transactional replication again (Yes, probably used
the same name). It seemed to be fine. I don't actually recall if the
publisher was showing up at that time or not. Someone later rebooted the
server with the subscriber on it and the jobs did not start back up on the
publisher (couldn't authenticate job owner, have now changed job owner).
That's the only reason why I noticed that there was even a problem. When I
tried to get it running again, it had to retry numerous times: The process
could not execute 'sp_replcmds' on 'servername'. But, I did get it running.
Due to all of this mess, I was going to try to use the validate subscription
thing but I couldn't find it. I ended up just doing row counts. That
combined with my understanding of the OLTP activity and checking with the
users of the subscriber, it seems to be fine - except that it is missing
from the replication monitor folder.
Plus, the conflict tables are called aonflict instead of conflict. I had
already researched this and believe that it is not anything to worry about.
I was thinking of waiting until after the sql server service was restarted
and if it is still not right, setting up a maintenance window to drop the
publication, disable this server as a publisher, drop the distribution
database, and start over.
What do you think?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u%23u8dCkfEHA.644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Can you give me a history of this problem.
> Note that if you script out publications and recreate them on the same
> server but in different databases and have the same snapshot agent and
> distribution agent name you can get a condition like this.
> By chance did you do something like this?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:u$PVj7jfEHA.720@.TK2MSFTNGP11.phx.gbl...
> sp_MSload_replication_status,
though.[vbcol=seagreen]
> identify
> can
patches,[vbcol=seagreen]
solve[vbcol=seagreen]
replication/publications
>
|||I think you are hosed.
You are correct in your assessment of the conflict table. The first conflict
table is named conflict_publicationName_tableName. The next conflict table
for that table is named aonflict_publicationName_tableName, the third
bonflict_publicationName_tableName, the 4th
donflict_publicationName_tableName, all the way up till you hit z, then its
zzonflict_publicationName_tableName, zzzonflict_publicationName_tableName,
etc.
I think your best bet is to remove replication using the wizards, and then
try to re-install it to get to a fresh base. Then recreate your publications
and subscriptions.
Is it possible for you to redistribute your snapshot? This is the safest way
to get your replication solution back to some level of consistency.
Is it also possible for you to restore your distribution database back?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"michelle" <michelle@.nospam.com> wrote in message
news:%23849jPkfEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Not exactly but what was done could have had the same effect...
> We had a problem a couple of weeks ago. I'll try to make a long story
short.
> Basically the only database on the server being replicated suddenly had
'no'
> tables showing up. This happened at the same time that the job that backup
> the transaction logs failed (SQL LiteSpeed). We have a support case open
> with MS but at this point they are asking that we pursue it with LiteSpeed
> (yet we do not have a support contract). We were getting assertion errors.
> Not sure if you care to hear about the details of that...
> This seemed like some kind of corruption in the database. We thought that
we
> would first try to detach and re-attach the files but since it was being
> replicated, we were unable to do so because it was being replicated. One
of
> the other DBAs here 'deleted everything from the distribution database' (I
> think that's what he said). I explained that the user database kept
> replication 'stuff' in it, too so that would not do any good. Too Late.
> Even a restore would require that we get rid of the existing database so
we
> restarted the sql server service to see what happened. Lo-and-behold, the
> database came back up.
> The next day we set up transactional replication again (Yes, probably used
> the same name). It seemed to be fine. I don't actually recall if the
> publisher was showing up at that time or not. Someone later rebooted the
> server with the subscriber on it and the jobs did not start back up on the
> publisher (couldn't authenticate job owner, have now changed job owner).
> That's the only reason why I noticed that there was even a problem. When I
> tried to get it running again, it had to retry numerous times: The process
> could not execute 'sp_replcmds' on 'servername'. But, I did get it
running.
> Due to all of this mess, I was going to try to use the validate
subscription
> thing but I couldn't find it. I ended up just doing row counts. That
> combined with my understanding of the OLTP activity and checking with the
> users of the subscriber, it seems to be fine - except that it is missing
> from the replication monitor folder.
> Plus, the conflict tables are called aonflict instead of conflict. I had
> already researched this and believe that it is not anything to worry
about.[vbcol=seagreen]
> I was thinking of waiting until after the sql server service was restarted
> and if it is still not right, setting up a maintenance window to drop the
> publication, disable this server as a publisher, drop the distribution
> database, and start over.
> What do you think?
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u%23u8dCkfEHA.644@.tk2msftngp13.phx.gbl...
> though.
the[vbcol=seagreen]
it[vbcol=seagreen]
> patches,
> solve
in
> replication/publications
>
|||Thanks for your advice. Not so sure about restoring the distribution
database. This is the only publication and yes, we would start the
subscription from scratch with a new snapshot. The subscription is just for
reporting so we can work it out.
Michelle
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u$m6VgkfEHA.252@.TK2MSFTNGP11.phx.gbl...
> I think you are hosed.
> You are correct in your assessment of the conflict table. The first
conflict
> table is named conflict_publicationName_tableName. The next conflict table
> for that table is named aonflict_publicationName_tableName, the third
> bonflict_publicationName_tableName, the 4th
> donflict_publicationName_tableName, all the way up till you hit z, then
its
> zzonflict_publicationName_tableName, zzzonflict_publicationName_tableName,
> etc.
> I think your best bet is to remove replication using the wizards, and then
> try to re-install it to get to a fresh base. Then recreate your
publications
> and subscriptions.
> Is it possible for you to redistribute your snapshot? This is the safest
way[vbcol=seagreen]
> to get your replication solution back to some level of consistency.
> Is it also possible for you to restore your distribution database back?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:%23849jPkfEHA.2916@.TK2MSFTNGP12.phx.gbl...
> short.
> 'no'
backup[vbcol=seagreen]
LiteSpeed[vbcol=seagreen]
errors.[vbcol=seagreen]
that[vbcol=seagreen]
> we
> of
(I[vbcol=seagreen]
> we
the[vbcol=seagreen]
used[vbcol=seagreen]
the[vbcol=seagreen]
I[vbcol=seagreen]
process[vbcol=seagreen]
> running.
> subscription
the[vbcol=seagreen]
> about.
restarted[vbcol=seagreen]
the[vbcol=seagreen]
> the
that[vbcol=seagreen]
> it
The[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
monitor/publishers
> in
>