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 1The 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]GOALTER 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]GOALTER INDEX [IND_TW_SLI_DETAIL_BYPASS_spid] ON [dbo].[TW_SLI_DETAIL_BYPASS] SET (ALLOW_PAGE_LOCKS = OFF)GO
have fun
Ben
It worked! Thanks so much.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gloria Ormsby Sent: Tuesday, September 27, 2016 9:20 AM To: Gloria Ormsby Subject: RE: [Tessitura Technical Forum] Maintenance Plan Reorganise index
Thanks Ken, I’ll give that a try!
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain Sent: Monday, September 26, 2016 11:14 PM To: Gloria Ormsby Subject: RE: [Tessitura Technical Forum] Maintenance Plan Reorganise index
Hi Gloria
From memory, the thing that's missing in the Standard edition is the ability to do an ONLINE REBUILD, which is a better choice than the standard OFFLINE REBUILD, of course, because it doesn't take the table out of commission while the rebuild is happening.
But since you can't do that, you'd just have to take that option out of the script, one way or another, so that it chooses OFFLINE REBUILD where it would currently choose the ONLINE REBUILD option
I think if you take this section where it makes that decision and comment out the lines as below, it should achieve that. (I haven't tested that, of course)
-----------------------------------------------------------------
Update #work
set action = 'REBUILD_OFFLINE'
where action = 'NOT_SET'
-- and (table_has_lob = 'Y' and index_type = 'CLUSTERED')
-- or index_has_lob = 'Y' -- can't rebuild online if it involves a LOB field
-- OR index_type = 'XML' -- can't rebuild XML indexen online, either
-- Update #work
-- set action = 'REBUILD_ONLINE'
-- where action = 'NOT_SET'
-------------------------------------------------------------------------
Ken
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 9/26/2016 8:30:03 PM
Hi Ken,
I installed your indexing scripts/job and unfortunately I don’t have the Enterprise edition of SQL so the job is failing on all the Maintain Indexes steps. Is there somehow I can change the procedure so it is Standard edition friendly?
Gloria
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain Sent: Thursday, June 30, 2016 12:00 AM To: Gloria Ormsby Subject: RE: [Tessitura Technical Forum] Maintenance Plan Reorganise index
We just run the standard SQLS Maintenance - Index Optimize task nightly on impresario_cci.
Since it's not being referred to by live website activity, it's fairly safe to run it overnight and let it do whatever it wants.
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 6/29/2016 5:00:45 PM
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
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.
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 6/28/2016 5:51:16 PM
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
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.
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 6/27/2016 12:26:31 PM
Would you be able to share your custom reindex procedure that is "tessiturized?"
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!