locked
Bug in SQL 2008 R2 sp_add_log_shipping_primary_database RRS feed

  • Question

  • This is not so much a question but more of a bug report with the sp_add_log_shipping_primary_database stored procedure provided by Microsoft in SQL 2008 R2. It may exist in other versions too. I don't know because I don't have access to those versions to check. I'm posting here in case anyone else runs into the same problem that I did, it may save them some grief.

    The symptom was when calling the stored procedure from inside a loop that tries to set up log shipping for multiple databases, I would end up with corrupted log shipping configurations (some of my LS_Backup job swould simply disappear on the primary server).

    The problem is that the master.dbo.sp_add_log_shipping_primary_database stored procedure falsely assumes that the OUTPUT parameter @primary_id is passed in as NULL.

    Here's the problem code from the sp_add_log_shipping_primary_database stored procedure...

        select @primary_id = primary_id 
        from msdb.dbo.log_shipping_primary_databases
        where primary_database = @database
        if (@primary_id is not null)
        begin
    <code snipped for brevity>
                delete msdb.dbo.log_shipping_primary_secondaries
                where primary_id = @primary_id
    <code snipped for brevity>
        end

     

    If @primary_id is not null when the procedure is called, and happens to contain the primary_id for another log shipping configuration, it will corrupt that configuration when it follows the IS NOT NULL logic. This is certainly not the intended behavior when the select returns no rows as it normally should.

    The code should either set @primary_id = NULL before the select, or better yet check @@rowcount to see if no rows were returned.

    The workaround is to set @primary_id to NULL before you call the stored procedure.

    Don't get bitten by the same bug that bit me. And if you stumble on this posting because you did, now you know why.


    Chuck
    Thursday, February 2, 2012 8:55 PM

Answers

  • Hi chuckh1958,

    Thank you for your post.

    Could you please submit a feedback to Microsoft?

    For submitting a feedback to Microsoft such issues, I would recommend to submit it to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. This connect site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft Connect.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tom Phillips Monday, February 6, 2012 1:34 PM
    Monday, February 6, 2012 2:57 AM

All replies

  • Hi chuckh1958,

    Thank you for your post.

    Could you please submit a feedback to Microsoft?

    For submitting a feedback to Microsoft such issues, I would recommend to submit it to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. This connect site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft Connect.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tom Phillips Monday, February 6, 2012 1:34 PM
    Monday, February 6, 2012 2:57 AM
  • Done. Bug id  723621.

     


    Chuck

    • Edited by chuckh1958 Monday, February 6, 2012 1:35 PM
    Monday, February 6, 2012 1:32 PM