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
Hi Ben
There's almost certainly no point in reorganising indexen on TW_SLI_DETAIL_BYPASS anyway - it's a work table that only holds small amounts of data (and only holds it while an order is open, I think, so the indexen only exist momentarily as well), so there's no chance for them to get fragmented, so they won't ever need to be re-organised....
In general, I think you're better off using a structured approach to rebuilding/re-organising indexes, rather than the one-size-fits-all approach used by the SSIS package task or the Tess utility, so that you only reorganise/rebuild indexes that need to be re-organised/rebuilt - certainly saves processing time and performance (and log generation).
Ken
Hi Ken,
Thank you for your great advice. I should think about some of local tables too.
What I fixed here is a standard SQL task. I think most of us maybe have the same issue as mine.
Sometime I opened the job history, all jobs were green, except this Reorganise index was red.
Now they all can be green again. someone suggests this on-off error is a MS bug.
Your advice should be my next step.
Thank you very much.
have fun.
Hi Ben,
I had the same issue when we moved to 9.0.0.2. I recreated my plans and have been ok since then. I had a standard SQL task go and “fix” all my indexes as needed as well.
Thank you have a good day!
Naomi Williams
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ben Gu Sent: Monday, March 22, 2010 2:12 AM To: Naomi Williams Subject: Re: [Tessitura Technical Forum] Maintenance Plan Reorganise index
From: Ken McSwain <bounce-kenmcswain5454@tessituranetwork.com> Sent: 3/22/2010 1:41:42 AM
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!
There is a generic reindex script in TASK\Shared Reports (solution 277 - contributed by Matthew Hoyt of St Louis Symphony), which follows current Microsoft recommendations about which indexes to reorg/rebuild. A good place to start.
We run something similar (but less generic, more tess-specialised) every night. That limits the hit from any particular run. Because we have SQL Enterprise, we can also try to have any needed rebuilds happen with the online option. That also helps reduce impact.
Would you be able to share your custom reindex procedure that is "tessiturized?"
Gloria
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.
Thanks Ken!
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
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 6/27/2016 12:26:31 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.
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.
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
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 6/28/2016 5:51:16 PM
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.
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?
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
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 6/29/2016 5:00:45 PM
One other piece of information. There are some steps that are succeeding. The ones failing are:
Maintain indexes - other tables
Maintain Indexes - medium tables
Maintain Indexes - t_web_session_Variable
Maintain Indexes - T_LIST_CONTENTS
Maintain Indexes - TX_PERF_SEAT
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'
-------------------------------------------------------------------------
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
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com> Sent: 9/26/2016 8:30:03 PM