locked
Rebuilding BizTak database's Indexes RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    We have SQL server 2005 EE x64 with SP3 having BizTalk server 2006 R2 databases. I went through the below links, before performing index rebuild for BizTalk databases.

    http://msdn.microsoft.com/en-us/library/cc296811%28BTS.10%29.aspx

    http://support.microsoft.com/kb/917845

    As per the above links, we CANNOT reorganize indexes for BizTalk databases and BizTalk supports only Index rebuilds:

    1. When NO BizTalk data processing is running

    2. We must use only the BizTalk provided stored procedures to rebuild the indexes

    and I read MSDN forums that we should


    3. Stop the SQL Agent and run the Index rebuild stored procedures (bts_RebuildIndexes, dtasp_RebuildIndexes )

    Questions:

    Is that true that we need to STOP SQL Agent before running the above Stored procedures?

    Did you stop the BizTalk data processing every time you rebuild indexes?

    I appreciate your inputs
    Monday, February 15, 2010 5:44 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    To answer your question: "Is that true that we need to STOP SQL Agent before running the bts_RebuildIndexes, dtasp_RebuildIndexes?" Yes that is true,  Microsoft only supports rebuilding database indexes during BizTalk Server downtime. (http://support.microsoft.com/default.aspx/kb/917845/). Did you stop the BizTalk data processing every time you rebuild indexes? Yes, see prevent SQL Server issue (see provided link).

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly

    BizTalk Server
    Monday, February 15, 2010 7:12 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    I would expect to also stop the BizTallk applications and hosts too as well as the Enterprise Single Sign-On Windows service because these can cause data changes that would affect your index rebuilding.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, February 16, 2010 4:14 PM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    To answer your question: "Is that true that we need to STOP SQL Agent before running the bts_RebuildIndexes, dtasp_RebuildIndexes?" Yes that is true,  Microsoft only supports rebuilding database indexes during BizTalk Server downtime. (http://support.microsoft.com/default.aspx/kb/917845/). Did you stop the BizTalk data processing every time you rebuild indexes? Yes, see prevent SQL Server issue (see provided link).

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly

    BizTalk Server
    Monday, February 15, 2010 7:12 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    I would expect to also stop the BizTallk applications and hosts too as well as the Enterprise Single Sign-On Windows service because these can cause data changes that would affect your index rebuilding.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, February 16, 2010 4:14 PM
    Moderator