Skip to main content

Log Reader Error On Adding Subscription

Ever tried to add a new subscription to an existing publication, using the "replication support only" or "initialize from backup" methods and go the following error?

The process could not execute 'sp_MSadd_replcmds' on <'Distribution Server>'. (Source: MSSQLServer, Error number: 1007)
Conversion failed when converting the nvarchar value '.' to data type int. (Source: MSSQLServer, Error number: 1007)
Batches were not committed to the Distributor. (Source: MSSQL_REPL, Error number: MSSQL_REPL22020)
The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

If so, you will know the internet is absolutely useless, filled with worthless workarounds or advice to just "rebuild replication"

Well, if you want an actual solution, just read on. Between SQL 2012 RTM and SQL 2012 SP4 there was a change made to the Distributor proc sp_MSadd_replcmds. This change was to facilitate a change in the format of log buffer, probably related to the number of publications you can have.

In the RTM version, the code that actually fails is as follows

select @originator_publication_id = CAST(CAST(substring(@cmdText, 1, 4) AS nvarchar) AS int),
    @next_valid_lsn_from_log = CAST(substring(@cmdText, 5, 10) AS binary(10))

In 2012 SP4 this becomes

-- if most high bit is set to 1 then interprete buffer as a new format 99 <= 0xFFFFFF9D (supports 2^32 ids), otherwise it's a backward compatibility for 99 <= ca (this is max id)
select @originator_publication_id = CASE WHEN CAST(substring(@cmdText, 1, 4) AS int) <= 0 THEN -CAST(substring(@cmdText, 1, 4) AS int) ELSE CAST(CAST(substring(@cmdText, 1, 4) AS nvarchar) AS int) END,
    @next_valid_lsn_from_log = CAST(substring(@cmdText, 5, 10) AS binary(10))

You can see now why you get the error in the log reader. As far as I can tell, having the publisher ahead of the distributor on point releases is what causes this - this makes sense since it is the log reader that fails. I don't believe the version of the subscriber has any effect here.

Anyway so now you know - always make sure you distributor patches in lock step with your publisher, even if the major version is the same.

Comments

  1. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
    python course in pune
    python course in chennai
    python course in Bangalore

    ReplyDelete
  2. Thank you so much. This was really helpful. I have been setting up permissions and playing around with log reader, but no luck. Your tip helped to resolve the issue.

    ReplyDelete
  3. I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.
    Best Devops training in sholinganallur
    Devops training in velachery
    Devops training in annanagar
    Devops training in tambaram

    ReplyDelete
  4. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.

    rpa training in bangalore | best rpa training in bangalore | rpa course in bangalore | rpa training institute in bangalore | rpa training in bangalore | rpa online training | rpa training in Chennai

    ReplyDelete
  5. Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.
    Advanced AWS Training in Chennai | Best Amazon Web Services Training in Chennai
    Best Amazon Web Services Training Course in Bangalore | AWS Training in Bangalore
    AWS Online Training and Certification | AWS Certification Course

    ReplyDelete
  6. Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.

    angularjs-Training in pune

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs Training in chennai

    automation anywhere online Training

    angularjs interview questions and answers

    ReplyDelete

  7. This is quite educational arrange. It has famous breeding about what I rarity to vouch.
    Colossal proverb. This trumpet is a famous tone to nab to troths. Congratulations on a career well achieved.
    This arrange is synchronous s informative impolite festivity to pity. I appreciated what you ok extremely here.

    Selenium training in bangalore
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training
    Selenium interview questions and answers


    ReplyDelete
  8. For Data Science training training in Bangalore, Visit:
    Data Science training in bangalore

    ReplyDelete
  9. I would definitely thank the admin of this blog for sharing this information with us. Waiting for more updates from this blog admin.
    salesforce Training in Bangalore
    uipath Training in Bangalore
    blueprism Training in Bangalore

    ReplyDelete
  10. Very nice post here and thanks for it .I always like and such a super blog of these post.Excellent and very cool idea and great blog of different kinds of the valuable information's.
    aws Training in Bangalore
    python Training in Bangalore
    hadoop Training in Bangalore
    angular js Training in Bangalore
    bigdata analytics Training in Bangalore

    ReplyDelete
  11. Really i found this article more informative, thanks for sharing this article! Also Check here

    Download and install Vidmate App which is the best HD video downloader software available for Android. Get free latest HD movies, songs, and your favorite TV shows

    Vidmate App Download

    Vidmate apk for Android devices

    Vidmate App

    download Vidmate for Windows PC

    download Vidmate for Windows PC Free

    Vidmate Download for Windows 10

    Download Vidmate for iOS

    Download Vidmate for Blackberry

    Vidmate For IOS and Blackberry OS

    ReplyDelete
  12. And also thanks for sharing this informative. Keep it Sir and I am waiting for your next post on your site blog.

    Best Training Institute in Bangalore BTM. My Class Training Bangalore training center for certified course, learning on Software Training Course by expert faculties, also provides job placement for fresher, experience job seekers.
    Software Training Institute in Bangalore

    ReplyDelete
  13. Really very happy to say, your post is very interesting to read. I never stop myself to say something about it. You’re doing a great job. Keep it up...

    Learn Hadoop Training from the Industry Experts we bridge the gap between the need of the industry. Softgen Infotech provide the Best Hadoop Training in Bangalore with 100% Placement Assistance. Book a Free Demo Today.
    Big Data Analytics Training in Bangalore
    Tableau Training in Bangalore
    Data Science Training in Bangalore
    Workday Training in Bangalore

    ReplyDelete

Post a Comment

Popular posts from this blog

SCCM 2012 - SMSPXE Error

I write this to help all you technical warriors out there who get shitty advice from the Microsoft Forums and other profound idiots about the 0x80092002 error in SCCM - usually to do with PXE booting but can manifest in other ways around DP -> MP communications.

The tricky one about this error is that it happens even if you are only using HTTP communication and not HTTPS/TLS. So to get an error about certificates is somewhat confusing.

What the uneducated masses of the internet will tell you is to re-install the PXE role over an over. This will do nothing.

Then they will tell you to re-install your management point and effectively re-build your entire infrastructure. This will work, but only because it is the nuclear option and will run the step that actually makes a difference as a matter of course.

So, here it is folks. If you have these kinds of errors in your logs:

PXE::MP_InitializeTransport failed; 0x80092002SMSPXE22/03/2017 2:45:58 PM4144 (0x1030) PXE::MP_ReportStatus failed…

Writing to the event log in .NET - the right way

As a DevOper, I spend a lot of time instrumenting code. This involves adding stuff like performance counters, tracing and logging.

One thing that constantly irks me when I go looking for ways or means to improve how I do my logging, is the complete and utter misinformation and misuse of the Windows Event Log. The problem relates to the canonical implementation of logging in .NET, log4net. Look at this method signature:

void Info(object message);
Note the lack of structure, the lack of context. Note the ability for a developer to put absolutely anything into the message object (which will be outputted as a string). When these types of messages are hooked up to an Event Log sink, the result is a generic message, usually in the Application log with a bit of free form text. Fairly useless for anyone to be able to filter and report on. The above style of logging is really more suited towards tracing and debug logging than actual operational logging,

Why you should write operational logs…