none
MS SQL Server 2008 R2 10.50.1600 - system stored procedure replaced by a 2005 version

    Question

  • Hello,

    I have a sql server 2008 enterprise running.
    As of 15/07/2011 I was no longer able to execute maintenance plans.

    After looking around I found a stored procedure - EXEC msdb.dbo.sp_verify_subsystems 1 - to rebuild the subsystems table.
    I noticed it would never insert record id 11 - SSIS.

    So I took a deeper look into the system stored procedured and to my big surpirse I found that this stored procedure was replaced by the sql server 2005 version.
    It was trying to get the DTS path from registery keys that did not exist in my version.

    Does anyone know how this could have happend?

    How can a system stored procedures from 2008 R2 be replaced by a sql server 2005 version?

    Is this a known issue and what other stored procedures might be changed?

     

    If someone else has this issue you should replace the store procedure by the proper 2008 R2 one.
    At this link you will find all the different versions of this SP: http://www.g-productions.nl/index.php?name=sp_verify_subsystems

    Thanks in advance

    Monday, July 25, 2011 1:58 PM

Answers

  • Hi ,

    With the four Windows update,SQL Server will not be affected. If no one manually changed this system store procedure or restore previous version msdb to your database, could you please check whether some DTS/SSIS packages will load and restore this system store procedure or msdb to your current database?

    Have you enabled change capture or audit on your SQL Server Database ?

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Peja Tao Monday, August 01, 2011 1:24 AM
    • Marked as answer by Peja Tao Friday, August 05, 2011 2:58 PM
    Wednesday, July 27, 2011 2:46 AM
  • hi,

     

    at the moment change capture is not enabled.

    I will have to look if someone has been playing around with SSIS packages which could have made a change to the system sp.

     

    Best regards,

     

    Raf.

    • Marked as answer by Peja Tao Friday, August 05, 2011 2:58 PM
    Monday, August 01, 2011 7:11 AM

All replies

  • I did not quite get your question, what version of SQL Server you are currently running? What changed from 15th of July? Did you install any new update?

     


    Dinesh B M [MSFT] -- This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 25, 2011 9:06 PM
  • We are running SQL server 2008 R2 on a Windows server 2008 R2.

    • product: Microsoft SQL Server Enterprise Edition (64-bit)
    • OS: Microsoft Windows NT 6.1 (7601)
    • Platform: NT x64
    • Version: 10.50.1600.1
    • Memory: 32755 (MB)
    • Processors: 12
    • Clustered: False

     

    15/07/2011 is the day we installed the patches for that week using WSUS.

    It were the following Four:

    1. Security Update for Windows Server 2008 R2 x64 Edition (KB2507938)
    2. Security Update for Windows Server 2008 R2 x64 Edition (KB2555917)
    3. Update for Windows Server 2008 R2 x64 Edition (KB2533623)
    4. Windows Malicious Software Removal Tool x64 - July 2011 (KB890830)

    That is the only change made to the machine.

     

    My question is why/or what could have caused the system stored procedure msdb.dbo.sp_verify_subsystems to get replaced by the sql server 2005 version?

     

    The updates are likely nothing to do with with it since we have another machine with the same setup which did not have this problem.
    Also none of the other dba changed anything or restored anything on the machine.

    Tuesday, July 26, 2011 7:37 AM
  • Hi ,

    With the four Windows update,SQL Server will not be affected. If no one manually changed this system store procedure or restore previous version msdb to your database, could you please check whether some DTS/SSIS packages will load and restore this system store procedure or msdb to your current database?

    Have you enabled change capture or audit on your SQL Server Database ?

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Peja Tao Monday, August 01, 2011 1:24 AM
    • Marked as answer by Peja Tao Friday, August 05, 2011 2:58 PM
    Wednesday, July 27, 2011 2:46 AM
  • hi,

     

    at the moment change capture is not enabled.

    I will have to look if someone has been playing around with SSIS packages which could have made a change to the system sp.

     

    Best regards,

     

    Raf.

    • Marked as answer by Peja Tao Friday, August 05, 2011 2:58 PM
    Monday, August 01, 2011 7:11 AM