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.