msrepl_transactions without references to msrepl_commands

If I understand your question correctly - you have data
in msrepl_transactions which doesn't correspond to data
in msrepl_commands. Possibly these rows refer to other
databases where the publications have been removed. The
procedure sp_MSdelete_publisherdb_trans is used by the
cleanup agent and I have seen cases where orphaned
records are left in the transactions table after a
publication has been removed.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
thanks.
can I use: sp_repldone with xact_id to kill these rows?
Regards Thomas
On Mon, 21 Feb 2005 02:13:16 -0800, "Paul Ibiso"
<Paul.Ibison@.Pygmalion.Com> wrote:

>If I understand your question correctly - you have data
>in msrepl_transactions which doesn't correspond to data
>in msrepl_commands. Possibly these rows refer to other
>databases where the publications have been removed. The
>procedure sp_MSdelete_publisherdb_trans is used by the
>cleanup agent and I have seen cases where orphaned
>records are left in the transactions table after a
>publication has been removed.
>Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||sp_repldone is related to the transaction log and the log
reader agent, and not really data that has laready
reached the distribution database. If the distribution
cleanup agent doesn't remove the extra transaction
records then I think you'll just have to leave them
there. If there is a huge amount, then contact PSS and
they'll hold your hand while you delete them.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

MSREPL_Commands TABLE

I don't think I ever resolved this issue & now I am quite concerned about my growing MSRepl_Commands table.
It has over 9 million rows.
What steps can I take to reduce the number of rows in this table?
Thanx!
JUDE
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jude" <judes@.email.uophx.edu> wrote in message news:utbKUSEtHHA.2752@.TK2MSFTNGP06.phx.gbl...
I don't think I ever resolved this issue & now I am quite concerned about my growing MSRepl_Commands table.
It has over 9 million rows.
What steps can I take to reduce the number of rows in this table?
Thanx!
JUDE
|||Expect this if you have anonymous subscribers. If this is a problem for you
consider using named subscribers if you can.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jude" <judes@.email.uophx.edu> wrote in message
news:utbKUSEtHHA.2752@.TK2MSFTNGP06.phx.gbl...
I don't think I ever resolved this issue & now I am quite concerned about my
growing MSRepl_Commands table.
It has over 9 million rows.
What steps can I take to reduce the number of rows in this table?
Thanx!
JUDE

MSRepl_Commands question

SORRY FOR THE LONG POST.
I am using transactional replication. I have an update process that is
run by users (not dbas). The update process among many other things
will delete replication, perform updates, and then reinstall
replication. This process has been working great for us (in testing).
However, there are times when the distribution agent hasn't finished
processing all the rows in msrepl_commands and the replication gets
dropped which causes lost (not replicated) commands.
We started by just running sp_browereplcmds with the proper
publication_database_id value to see if any commands were outstanding.
The thought was to check the recordset, if empty, then proceed, else
wait, until specified time then cancel the update with a nice message
about waiting until replication was complete. Great, right? Wrong.
Even after replication is finished and EVEN after the cleanup agent is
run (we are considering running it manually and rechecking the list),
there are commands STILL in the table. They all have command type
values of -2147xxxx and they all appear to be snapshot commands.
Sorry for the long post, but here's the question: Does anyone know
where I can find a key to these values? I would like to just ignore any
-2147xxxx type command in the table and proceed with dropping
replication.
Any help would be appreciated!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
this is as near as I can figure
-2147483641 directory check
-2147483598 file check
-2147483597 unknown
-2147483646 apply schema
-2147483646 apply index
-2147483645 sync command
-2147483596 unknown
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"PBarbin" <pbarbin@.nospam.com> wrote in message
news:%23lDmE10aEHA.2516@.TK2MSFTNGP10.phx.gbl...
> SORRY FOR THE LONG POST.
> I am using transactional replication. I have an update process that is
> run by users (not dbas). The update process among many other things
> will delete replication, perform updates, and then reinstall
> replication. This process has been working great for us (in testing).
> However, there are times when the distribution agent hasn't finished
> processing all the rows in msrepl_commands and the replication gets
> dropped which causes lost (not replicated) commands.
> We started by just running sp_browereplcmds with the proper
> publication_database_id value to see if any commands were outstanding.
> The thought was to check the recordset, if empty, then proceed, else
> wait, until specified time then cancel the update with a nice message
> about waiting until replication was complete. Great, right? Wrong.
> Even after replication is finished and EVEN after the cleanup agent is
> run (we are considering running it manually and rechecking the list),
> there are commands STILL in the table. They all have command type
> values of -2147xxxx and they all appear to be snapshot commands.
> Sorry for the long post, but here's the question: Does anyone know
> where I can find a key to these values? I would like to just ignore any
> -2147xxxx type command in the table and proceed with dropping
> replication.
> Any help would be appreciated!
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks for the reply. As far as I can tell, transactional replication
commands are of type 30.
As an alternate question, can anyone think of a way that I can be SURE
that transactional replication is finished before deleting it and
readding?
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Paul,
you could compare the xact_seqno in MSDistribution_history (last processed
transaction sequence number) to the values in msrepl_commands. Presumably,
if the last row in each table has the same xact_seqno (after reformatting
slightly), then you're ok.
HTH,
Paul Ibison
|||Paul, that may help tremendously! I'll look into it. Have you noticed
that sp_browsereplcmds shows commands even when there are none to be
processed. I was hoping it was just because we were in a test
environment and we weren't always playing by the rules, but I didn't
want to ship this to all customers without being pretty confident that
my solution would work. I'll try this get back...
Thanks.
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Background
When my program is updated in the future, the first step will be to
delete the transactional replication, make all database changes to the
publisher and the subscriber and then reinitialize replication. We want
to make sure that the distribution agent has finished processing all
outstanding transactions before dropping it.
Problem
We considered checking the status of the distribution agent but realized
that we couldn't. One reason is that it never "finishes". This agent
runs continuously. Also, its possible to have the status of the agent
as "Idle" even when there are commands left to be processed (this
process is asynchronous).
So, we decided to query the system table, MSRepl_Commands, directly.
This table has a few interesting problems. One is that commands are not
dropped immediately after being processed by replication. Another is
that rows exist in this table for both snapshot and transactional
replication. As usual, the only identifier to distinguish them was not
documented and we were not completely sure which rows to filter. The
stored procedure that Microsoft recommends using doesn't take these
items into consideration and will return rows when transactional
replication is "finished".
Solution
Profiling the agent gave us all the information we needed. We found
that the subscription database contains a table that tracks the last
transaction number processed for each distribution agent that hits the
database (we only have 1). Now we can filter out all the old commands
by checking their transaction number against this value. Also, we saw
that one of the stored procedures defines a snapshot bit (0x80000000)
and uses a bitwise AND on this undocumented "Type" column to see which
commands are snapshot and which are not. Using both of these pieces of
info, we are able to query the table to accurately determine the status
of transactional replication.
Hope this helps somebody. I find it amazing how little documentation
exists regarding replication. If it weren't for the great posters (Paul
I. and Hilary C) on this group, a LOT of us would be in trouble.
Thanks!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

MSREPL_COMMANDS Question

How does one purge the Msrepl_commands table in the Distribution
database? My table has 3447147 rows & I think this is excessive. Any
suggestions?
query MSDistribution_status in your distribution database - this will help
you to understand where the problem is
Otherwise run the distribution clean up agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jlshoop@.hotmail.com> wrote in message
news:1129691264.949911.252410@.z14g2000cwz.googlegr oups.com...
> How does one purge the Msrepl_commands table in the Distribution
> database? My table has 3447147 rows & I think this is excessive. Any
> suggestions?
>

MSrepl_commands question

Hi,
I have 750,000 commands in the table MSrepl_commands. The
cleanup agent hasn't removed them, although they have all
gone to my one subscriber, and I don't have anonymous
subscribers. What am I missing??
Rgds,
BB
You should be able to delete them manually. You will have to delete them
from the msrepl_transactions and msrepl_commands tables.
Before you do this, I would try to run the clean up manually.
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a4401c4a54e$21714500$a501280a@.phx.gbl...
> Hi,
> I have 750,000 commands in the table MSrepl_commands. The
> cleanup agent hasn't removed them, although they have all
> gone to my one subscriber, and I don't have anonymous
> subscribers. What am I missing??
> Rgds,
> BB
|||Thanks Hilary,
running
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
0, @.max_distretention = 0
has me worried - what if another command comes in just
before I run it - presumably it will be deleted?
Also, have you ever seen this behaviour before - ie
commands retained in this table? Any idea what causes it?
Thanks,
BB
|||only commands that are replicated to the subscriber will be deleted.
Undistributed commands will not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
|||btw you can always pipe the msrepl_commands and msrepl_transaction tables to
backup tables before you do the deletes or manually run the clean up
command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
|||Thanks Hilary - any idea why it happens?
TIA,
BB
|||Sometimes the distribution clean up agent gets locked and is picked as the
dead lock victim. This normally leads to even more locking as the cleanup
agent has to do more work. You can try to run your distribution agent at a
greater frequency, ie every minute, or stop your log reader and distribution
agent and then run it.
On high volume systems when you constantly get this type or error you might
consider a remote distributor.
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:2bed01c4a564$10f7bb80$a501280a@.phx.gbl...
> Thanks Hilary - any idea why it happens?
> TIA,
> BB
|||I think you would be best to use a maxium_retention period of 24. Paul
Ibison pointed out to me that a value of 0 will remove undistributed
transactions.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
sql

MSRepl_Commands

I followed your advise Hilary,
"query MSDistribution_status in your distribution database - this will help
you to understand where the problem is
Otherwise run the distribution clean up agent."
Distribution Agent doesn't appear to be clearing these old transactions.
Is there a way to clean out the old Undelivered commands that I told the Agent to skip/ignore?
Any suggestions on how I can get MSRepl_Commands table back under 3 million rows?
Thanx!
JLS
A quick and dirty way to do this is to drop the subscriptions and then
recreate them using a nosync subscription.
You can also manually delete them if you are very careful and know which
ones to remove. If you are using anonymous subscriptions they will hang
around longer than if you are using named subscriptions. Can you convert
them to named subscriptions?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%231mF$ZW4FHA.400@.TK2MSFTNGP09.phx.gbl...
I followed your advise Hilary,
"query MSDistribution_status in your distribution database - this will help
you to understand where the problem is
Otherwise run the distribution clean up agent."
Distribution Agent doesn't appear to be clearing these old transactions.
Is there a way to clean out the old Undelivered commands that I told the
Agent to skip/ignore?
Any suggestions on how I can get MSRepl_Commands table back under 3 million
rows?
Thanx!
JLS
|||I don't use anonymous subscriptions.
What is the harm in deleting from msrepl_commands?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uToQ7yb4FHA.3948@.TK2MSFTNGP09.phx.gbl...
A quick and dirty way to do this is to drop the subscriptions and then
recreate them using a nosync subscription.
You can also manually delete them if you are very careful and know which
ones to remove. If you are using anonymous subscriptions they will hang
around longer than if you are using named subscriptions. Can you convert
them to named subscriptions?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%231mF$ZW4FHA.400@.TK2MSFTNGP09.phx.gbl...
I followed your advise Hilary,
"query MSDistribution_status in your distribution database - this will help
you to understand where the problem is
Otherwise run the distribution clean up agent."
Distribution Agent doesn't appear to be clearing these old transactions.
Is there a way to clean out the old Undelivered commands that I told the
Agent to skip/ignore?
Any suggestions on how I can get MSRepl_Commands table back under 3 million
rows?
Thanx!
JLS

MSRepl_Commands

Hi everyone
has anyone come across / developed a way of transforming the commands
in MSRepl_Commands so that, for instance, and Update becomes a Delete
or Insert?
thanks in adveance for abny assists!
Are you requiring an explanation of how this occurs in replication?
If so, this is referred to as a deferred update - see
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||MS_replcommands is a queue. You can modify this queue to delete commands.
Modifying them is difficult as they are in binary. Your best bet is to
ensure that your updates are not replaced by an insert delete pair.
Please refer to the following kb articles for more information.
http://support.microsoft.com/default.aspx/kb/302341
and
http://support.microsoft.com/kb/238254
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RedCamel" <RichardJGilbert@.hotmail.com> wrote in message
news:1163426512.098950.159840@.m73g2000cwd.googlegr oups.com...
> Hi everyone
> has anyone come across / developed a way of transforming the commands
> in MSRepl_Commands so that, for instance, and Update becomes a Delete
> or Insert?
> thanks in adveance for abny assists!
>