• Replies 16 replies
  • Subscribers 336 subscribers
  • Views 142 views
  • Users 0 members are here
Related

Maintenance Plan Reorganise index

Ben Gu
$organization

Hi All,

Good day.

our Reorganise index Maintenance paln always failed for a while.

I never figure out what is wrong before.

today I used the SSIS package "Reorganize Index Task" to run the task, finally find out what cause the job failure.

the error message is :

 

Msg 2552, Level 16, State 1, Line 1
The index "IND_TW_SLI_DETAIL_BYPASS_spid" (partition 1) on table "TW_SLI_DETAIL_BYPASS" cannot be reorganized because page level locking is disabled.

This is v9.0 release Notes:

985

Indexes on the following tables have been reorganized to improve performance: TX_PERF_PMAP, TX_APPEAL_MEDIA_TYPE, T_MEMB_ORG, T_EADDRESS, T_ROLE, T_PRODUCTION, T_CUST_ACTIVITY, T_ISSUE_ACTION, T_ACCOUNT_DATA and T_LINEITEM.  In addition the index on the work table TW_SLI_DETAIL_BYPASS has been changed to disallow page_locks to improve concurrency.  Indexes were added to T_BATCH and T_SEAT to cover some frequently occurring queries.  Two indexes on T_LINEITEM were removed, as they were used very infrequently.

 

 

 Final solution:

USE [impresario]
GO
ALTER INDEX [IND_TW_SLI_DETAIL_BYPASS_spid] ON [dbo].[TW_SLI_DETAIL_BYPASS]  SET (ALLOW_PAGE_LOCKS = ON)
GO

run my Reorganise index Job


USE [impresario]
GO
ALTER INDEX [IND_TW_SLI_DETAIL_BYPASS_spid] ON [dbo].[TW_SLI_DETAIL_BYPASS]  SET (ALLOW_PAGE_LOCKS = OFF)
GO

 

have fun

Ben

Parents
  • Former Member
    Former Member $organization

    Ken,

     

    Do you include impresario_cci? Does that need to be reindexed?

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
    Sent: Tuesday, June 28, 2016 6:41 PM
    To: Gloria Ormsby
    Subject: RE: [Tessitura Technical Forum] Maintenance Plan Reorganise index

     

    Hi Gloria

    We've never had an issue with internal tables, as far as I'm aware.

    Since their names can't be referenced in transact-sql statements, and only their metadata is available for viewing, I would assume that they're entirely unaffected (directly) by stored procs of any kind, pretty much by definition. So our script never tries to touch them.

    Ken

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 6/28/2016 5:51:16 PM

    Ken,

     

    What do you do about internal tables? I’m getting errors and my SQL consultant describes it here:

     

    We have a weekly Index Maintenance procedure for the [impresario] DB’s which works fine for all of the system tables and user tables. 

    If zero “Internal Tables” were active at the time when we did our Index Maintenance, then the Index Maintenance job would succeed. 

    Solving the current issue requires me to figure out how to deal with any (temporarily active) “Internal Tables” in the DB. 

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
    Sent: Monday, June 27, 2016 9:16 PM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Maintenance Plan Reorganise index

     

    Hi Gloria

    I've posted the current version of the scripts to my Files here

    It's been changed a bit since this original forum message, but fundamentally the same process.

    The major change, courtesy of my colleague Rob Dunn, was to run it in several chunks with a small break between each, so that it if has to do a major rebuild, the database gets a chance to take a few deep breaths every now and again, to let the log catch up and avoid making the temp db grow too much.

    That means there's now four components rather than just the one proc - two procs, a local table (which is stored in another database to avoid cluttering up Impresario - you'd need to modify that location obviously.) and a job that runs the procs in sequence.

    Ken

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 6/27/2016 12:26:31 PM

    Hi Ken,

    Would you be able to share your custom reindex procedure that is "tessiturized?"

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Reply
  • Former Member
    Former Member $organization

    Ken,

     

    Do you include impresario_cci? Does that need to be reindexed?

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
    Sent: Tuesday, June 28, 2016 6:41 PM
    To: Gloria Ormsby
    Subject: RE: [Tessitura Technical Forum] Maintenance Plan Reorganise index

     

    Hi Gloria

    We've never had an issue with internal tables, as far as I'm aware.

    Since their names can't be referenced in transact-sql statements, and only their metadata is available for viewing, I would assume that they're entirely unaffected (directly) by stored procs of any kind, pretty much by definition. So our script never tries to touch them.

    Ken

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 6/28/2016 5:51:16 PM

    Ken,

     

    What do you do about internal tables? I’m getting errors and my SQL consultant describes it here:

     

    We have a weekly Index Maintenance procedure for the [impresario] DB’s which works fine for all of the system tables and user tables. 

    If zero “Internal Tables” were active at the time when we did our Index Maintenance, then the Index Maintenance job would succeed. 

    Solving the current issue requires me to figure out how to deal with any (temporarily active) “Internal Tables” in the DB. 

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
    Sent: Monday, June 27, 2016 9:16 PM
    To: Gloria Ormsby
    Subject: Re: [Tessitura Technical Forum] Maintenance Plan Reorganise index

     

    Hi Gloria

    I've posted the current version of the scripts to my Files here

    It's been changed a bit since this original forum message, but fundamentally the same process.

    The major change, courtesy of my colleague Rob Dunn, was to run it in several chunks with a small break between each, so that it if has to do a major rebuild, the database gets a chance to take a few deep breaths every now and again, to let the log catch up and avoid making the temp db grow too much.

    That means there's now four components rather than just the one proc - two procs, a local table (which is stored in another database to avoid cluttering up Impresario - you'd need to modify that location obviously.) and a job that runs the procs in sequence.

    Ken

    From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>
    Sent: 6/27/2016 12:26:31 PM

    Hi Ken,

    Would you be able to share your custom reindex procedure that is "tessiturized?"

     

    Gloria




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Children