June 2, 2013 at 5:39 pm
I prefer RedGate to TableDiff. Can limit the search scope. But the main reason is some of our subscribers have extra columns (part of reporting package) and tablediff doesn't handle this very well.
June 3, 2013 at 9:50 pm
Hello All,
I get errors as:
Row not found for update or delete commands
PK constraint for insert commands (specially when I applied the data discrepency script from RedGate andd the record is already on the subscriber)
Can I comment the error code in sp_MSins stored procedure as well? I know the article says I can do it for update and delete as the record doesn't exists on the subscriber. But I don't know if i can do it for insert as well. I already ran the red gate compare and deployed the script for some of the articles. And I know the insert replication commands are failing due to PK violation. That's the reason I have to ask this question.
Also, how to do check how may commands are in the queue for replication (didn't replicate yet)? How do I check how many transactions SQL is replicating daily?
Thanks,
-Mohan
June 4, 2013 at 12:07 am
Mohan
It is not uncommon to change the logic of sp_MSins... to: if pk exist then update row else insert row. (This is to cater for very specific environment needs(for example you know historic data out of synch, but it is not a major concern) This should be used with caution.)
It depends on what info you need:
--per distributor (Take note there is a bug (Depends how you look at it), which was fixed number of times but back again,
--where the last distributed tran commands not deleted from distribution..msrepl_commands)
select COUNT(*) from distribution..msrepl_commands c (nolock)
--per article
select * from from distribution..MSdistribution_stautus with (nolock)(to see breakdown per article, link to other tables for additional info)
--per publ db
select PublDBID=t.publisher_Database_id, count(*)
from distribution..msrepl_transactions t (nolock)
inner join distribution.dbo.msrepl_commands c (nolock) on t.publisher_database_id = c.publisher_database_id and t.xact_seqno = c.xact_seqno
group by t.publisher_Database_id
order by 2 desc
--from the above you can found al kinds of info, like:
--use one or more of the PublDBID values of above query in the following where clause
--to display more replication detail for PubDBs
select Pub = pb.publication, PubID = pb.publication_id , PubImSynch = pb.immediate_sync, PubRetion = pb.retention, PubSrv = ps.name, PubDB = sb.publisher_db, PubDBId = sb.publisher_database_id, PubObj = ar.source_object
,SubSrv = ss.name, SubDB = sb.subscriber_db, SubObj = ar.destination_object,DistrID = da.id ,DistrJob = da.name
, st.DelivCmdsInDistDB , st.UndelivCmdsInDistDB, SQLCmdToStartJob = 'msdb.dbo.sp_start_job ''' + da.name + ''''
from distribution.dbo.MSdistribution_status st (nolock)
inner join distribution..MSsubscriptions sb (nolock)on st.agent_id = sb.agent_id and st.article_id = sb.article_id
inner join distribution..MSarticles ar (nolock)on sb.article_id = ar.article_id and sb.publisher_id = ar.publisher_id and sb.publication_id = ar.publication_id
inner join distribution..MSpublications pb (nolock)on sb.publisher_id = pb.publisher_id and sb.publication_id = pb.publication_id
inner join master.sys.servers ss (nolock)on sb.subscriber_id = ss.server_id
inner join master.sys.servers ps (nolock)on pb.publisher_id = ps.server_id
inner join distribution..MSdistribution_agents da (nolock)on sb.agent_id = da.id and sb.subscriber_id = da.subscriber_id and sb.subscriber_db = da.subscriber_db
where sb.publisher_database_id in (insert PublDBID values here )
Please see me first reply to this article.
Regards
Stephan
June 4, 2013 at 12:20 am
Mohan
Sorry I missed the last question.
Depends on your needs, but if your replication is configure to keep enough history you will find a lot of info in MSdistribution_history table for an ad hoc request.
June 6, 2013 at 8:55 am
Thank you for the reply Stephan.
You provided very good information which I wasn't aware being new to T-Replication.
One question I have is my select * from distribution.dbo.MSrepl_commands table is very huge. Are all the commands waiting to replicate?
I don't see those commands in sp_repltrans results. And when I pick one of the @xact_seqno + command_id and query in sp_browsereplcmds, the update command match with the pubs and subs databases.
Can you please provide me information on these tables?
select * from distribution.dbo.MSrepl_commands
select * from distribution.dbo.MSrepl_transactions
Thanks again for your help! You ROCK!!!
June 25, 2013 at 5:38 am
Have to come back to say thanks. I had a glance at this article the other day. Today it did save me.
June 26, 2013 at 9:06 am
Mohan
I am not a regular user, but you should look around somewhere you should be able to submit questions and request help with specific challenges. This is not related to Edward's origin topic and should not be asked here. My apologies to Edward.
Again I am not sure of your question and what you need to know: When a transaction entered replication, it is stored in MSrepl_transactions.(As in when the log agent read it from the transaction log, process and load into the distribution db). The command/statements in replication are stored in MSrepl_commands. (in correct order and group by transaction.) A statement might be split and saved over multiple lines/rows, this will be indicated by partial_command. "Sp_repltrans"; an extended procedure in the publication db, display info of the transactions in this db's transaction log that is not sent to the distribution db yet.(to be in MSrepl_commands the log reader must have proccesed them and I would not expect to show in this result. It is either processed or not processed yet.) "Are all the commands waiting to replicate?" This depends on your configuration and environment. I am unable to answer, as I do not know your configuration and environment.
In regards to "MSrepl_commands table is very huge": From time to time a dba might encounter a problem where MSrepl_commands grow huge and needs to intervene. Here follow a few quick pointers just to get you started. (might vary to specific circumstances and different environments and I assume yours is a typical standard setup).
Ensure all replication components is running without errors.
Ensure the SQL Agent job (Distribution clean up: distribution), which should delete old commands from MSrepl_commands, is running regular and complete without errors.
Use something like the next statement to determine on which PublDBs to focus:select t.publisher_Database_id, CmdCnt=count(*) from distribution..msrepl_transactions t (nolock) inner join distribution.dbo.msrepl_commands c (nolock) on t.publisher_database_id = c.publisher_database_id and t.xact_seqno = c.xact_seqno group by t.publisher_Database_id order by 2 desc
Check that all the old records was actualy deleted by the cleanup job(Here follow sample code, to see what must be left in outbound queue. Please replace the db id with an id from prevous result set as needed. This should be zero or close to it, else check why this replication is so far behind.): use distribution; declare @PublDBID int, @max_seqno varbinary(16), @Tmp varchar(max); set @PublDBID = 41; exec sp_MSmaximum_cleanup_seqno @PublDBID, NULL, @max_seqno OUTPUT; select @Tmp = 'No of commands still in out-bound queue which should replicate : ' + convert(varchar(25),Count(*)) from MSrepl_commands with (nolock) where publisher_database_id = @PublDBID and xact_seqno <= @max_seqno; print @max_seqno; print @Tmp
Do some research on immediate_sync and allow_anonymous options. This affects the cleanup behavior of transactional replication. If this option is set to true, the transactions will be retained for max retention period instead of getting deleted as soon as it is replicated to all the subscriptions which had subscribe to it. To check for these:select * from distribution..MSpublications where publication_type = 0 and immediate_sync = 1
If you need to change this options:use YourDBName; EXEC sp_changepublication @publication = 'YourPublName',@property = 'allow_anonymous', @value = 'false';
EXEC sp_changepublication @publication = 'YourPublName',@property = 'immediate_sync', @value = 'false'
Also see statement in my prevous reply. If there is high number of commands in MSrepl_commands, but not much in corresponding UndelivCmdsInDistDB and not much to delete by cleanup job check for the behavour mention in previous reply which does not delete the last repilcated transaction's commands. If this is the case just update some thing to replicate a new small transaction which will now enable job to delete old big transactions commands the next time cleanup job runs.select xact_seqno, CmdCnt=count(*) from distribution..msrepl_commands (nolock) where publisher_database_id = 41 group by xact_seqno order by xact_seqno asc;
Stephan
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply